Excel has autocorrect data input turned on by default. After pasting the number into the cell, it is automatically replaced with the date. if the Excel number has a similar length as the date, it will go crazy and change the number to a date.
This changes the contents of the cell. A strange date appears instead of a number. These are often dates from hundreds of years ago or from the future.
Let’s see how to disable AutoFormat for dates in Excel. I know a few ways that I want to share with you.
The easiest way to turn off number-to-date conversion is to turn off AutoCorrect. AutoCorrect is on, but you can turn it off.
In order to disable autocorrect, you need to find the responsible option in the ribbon menu.
Click File> Options> Proofing. Click the AutoCorrect Options button. The AutoCorrect window appears.
Go to the AutoFormat As You Type tab. Deselect all checkboxes.
This change will have two important consequences for you:
- Excel will stop recognizing numbers as dates automatically, which will solve the problem you are looking for a solution here
- all other autocorrect options will not work
Set the data type manually
I have another solution for you. If you don’t want to turn off autocorrect, you can do something else.
Suppose you have a table into which you will paste numeric data. Let’s not let Excel convert numbers to dates.
Right-click on the cell where you want to paste the data. Select the Format Cells option.
Manually select the Number format.
The data format in your cell has been set to numeric and will remain so.
When pasting data into a cell, use the paste special option as values. To do this, when pasting data, right-click on the cell to which you are pasting. From Paste Options, select Values.
The goal has been achieved. Data format did not change to date.
The downside to this method is that it is quite cumbersome, because you have to set the data format manually each time and then paste specifically.
Changing the data format after pasting
Let’s move on to another way to deal with the tedious swapping of numbers to dates.
Suppose you pasted the data and the numbers changed to dates. To change the contents of a cell to numbers you should remove the cell formatting.
In the earlier ways, we changed the data format before pasting the data. In this way, we will change the data format after pasting.
To remove cell formatting, select the corresponding option in the ribbon menu. On your right side you can find the Clear button. Click on it and select Clear Formats.
Tip: The keyboard shortcut is ALT + H + E + F
When you use this option, the cell’s formatting is removed and the cell’s contents change to a number.
This method has two disadvantages:
- Every time after pasting, you need to remember to remove cell formatting
- After using this method, you will remove any kind of cell formatting. Not only the data format will change. You will also delete the font format, border, color, etc.
Pasting an apostrophe or space
I know one more way. This time it’s a cheat on Excel. Excel will not change the data type when it recognizes something in a cell that it cannot match with any data type it knows.
There are several such characters that you can enter into the cell just before entering the numbers.
When you type them into a cell and then paste the number, Excel won’t change it to the date. Excel will just go crazy because it doesn’t associate these characters with a date.
The downside of this method is that you type unnecessary and nonsensical characters into the cells. But maybe this idea is worth applying?
Using VBA code
You can use VBA (Visual Basic for Applications) to prevent Excel from changing numbers to dates by adding a macro to your workbook. The macro will convert the cells in question to text format before the data is entered, which will prevent Excel from automatically formatting the cells as dates.
Here’s an example of a macro that will convert the contents of a selected range of cells to text format:
Selection.NumberFormat = "@"
To use this macro, follow these steps:
- Open the workbook that contains the data you want to convert to text.
- Press “Alt + F11” to open the VBA editor.
- In the editor, select “Module” from the “Insert” menu.
- Paste the macro into the module.
- Close the VBA editor and return to the Excel worksheet.
- Select the range of cells you want to convert to text.
- Press “Alt + F8” to open the macro dialog box, and then select the “ConvertToText” macro.
- Click “Run” to run the macro and convert the selected cells to text format.
This is a simple example, but you can modify the macro to suit your specific needs. By using a macro to prevent Excel from changing numbers to dates, you can automate the process and ensure that your data is preserved in the format you intended.