How to Stop Excel from Converting Certain Text Values to Dates

The features in Excel are really smart and are very useful in many instances. These features, however, can be frustrating. For example, Excel automatically converts numbers to dates when you try entering the numbers as it assumes that you want to enter a date. Though you may want this, you want to enter the numbers as they are in some cases. Two common ways can stop Excel from converting numbers to dates.

The Reason Excel converts some numerical values to Dates

Excel automatically converts numbers with valid date formats to dates, which sometimes helps. For example, if you type 3/6/2020, Excel changes this value to 44012, the numerical value.

How to stop this from happening

Excel can be stopped from changing a text string to dates by trying to convince it that they are not numbers. It can be done by changing the format to Text. In this case, Excel is convinced that the number you have entered is in text format and will therefore not convert it to date. Follow the simple steps below;

1. Select the cells that you need to apply this action

2. Go to the Home Tab

3. Select the Number Group, then click the dialog box launcher. You can also select Ctrl + 1

4. Go to the Format Cells dialog box. Choose Text

5. Press OK

The format of the cell is then changed to text format. Excel will, therefore, not convert the numbers you enter into dates. The Format Cells "dialog box" can also be opened by selecting the cell, then right-click and selecting Format Cell.

You can also use the Apostrophe before the Number if you have to enter a number in a few cells. In this case, you will add the apostrophe sign before you type any number. Adding an apostrophe makes excel assume the Number a text. The numbers will be aligned to the left, showing that they are texts. It is important to note that numbers are usually aligned to the right while texts are aligned to the left by default.

Using Apostrophe also allows you to use formulas like MATCH. This method can be used to transform already existing dates into texts. Just add the Apostrophe before the date, and it will change to a text. A green triangle appears on the top-left part of the cell to confirm whether the numbers have been saved as texts. Once you see it, click on the Green Triangle>Select Ignore Error, and it will go away.

You may also want to convert the date to Text in Excel. If you have a dataset that you want to change the dates to Text, use the following simple steps;

1. Select the cell that you want to perform this action

2. Click on Data Tab, then go to Data Tools> Text to Column

2. Select Delimited> Click Next


3. Deselect all the other options in the delimiter box> Click Next

4. Choose Text, then specify cell destination

5. Click Finish