Why is my Excel cell changing my numbers?

In your early days of working in Excel, you probably saw the occasional cell full of number signs (you might call them pound signs or hash marks). Here’s what you can do to get rid of number signs in Excel.

Number Signs in Excel

Usually, you’ll see cells full of number signs if the column is too narrow to show the full number. It could be misleading if Excel displayed part of a number, so number signs are displayed instead, to alert you to the problem.

Widen the Column

To fix that problem, widen column C, so there is room for the full number. The quickest way to make the column the correct width is to double-click the line between the headings for columns C and D.

Text Cells Filled With Number Signs

What if the column is wide, and the cell contains text, not a number – and it’s full of number signs?

In the screen shot below, cell B2 is selected. In the formula bar, you can see the text from cell B2, and the letters are displayed correctly there.

However, instead of showing the text, cell B2 is filled with number signs, in a single row at the top of the cell.

• Cell B2 is set for Wrap Text, but the number signs are not wrapping.
• Cell B2 is formatted as Text, instead of Number or General.

Wider Column Doesn’t Help

There is a lot of text in that cell, but Excel should be able to handle up to 32,000 characters. In cell A2, there is a LEN formula: =LEN(B2)

The result of that formula shows that there are 1100 characters in cell B2. That is well under the Excel limit.

In this case, widening the column doesn’t fix the problem – you’ll just end up with a wider column of number signs.

Fix Text Cells With Number Signs

In this case, the problem is the cell’s Number format — it is set as Text.

To fix text cells that are showing number signs:

• Set the cell format to General, instead of Text.

It’s a strange solution, because Text seems like the logical choice, when formatting a cell that contains text.

Even if Excel now thinks that your numbers are text, our data visualization tool Datawrapper will be able to recognize them. As soon as you upload the numbers to Datawrapper, it guesses what data format your columns have. 

If that guess is wrong, you can easily change the format again. Click on the column, then choose the correct format in the drop-down menu that pops up on the left side:

Excel has some really smart features that can be really useful in many cases (and sometimes it could be frustrating).

One such area is when you enter numbers in Excel. Sometimes, Excel automatically changes these numbers to dates.

For example, if you enter 1/2 in Excel, Excel will automatically change this to 01-02-2020 (or 02-01-2020 if you’re in the US)

Similarly, if you enter 30-06-2020, Excel assumes you want to enter a date, and it changes this to a date (as shown below)

While this may be what you want in most cases, but what if I don’t want this. What if I simply want the exact text 30-06-2020 or the fraction 1/2.

How do I stop Excel from changing numbers to dates?

That’s what this tutorial is about.

In this Excel tutorial, I will show you two really simple ways to stop Excel from changing numbers to text.

But before I show you methods, let me quickly explain why Excel does this.

This Tutorial Covers:

• Why does Excel Changes Numbers to Date?
• Stop Excel from Changing Numbers to Dates Automatically
  • Change the format to text
  • Add an Apostrophe before the Number

Why does Excel Changes Numbers to Date?

While it can be frustrating when Excel does this, it’s trying to help.

In most of the cases, when people enter numbers that can also represent valid date formats in Excel, it will automatically convert these numbers into dates.

And this just doesn’t mean that it changes the format, it actually changes the underlying value.

For example, if you enter 3/6/2020 (or 6/3/2020 if you’re in the US and using the US date format), Excel changes the cell value to 44012, which is the numerical value of the date.

Also read: How to Convert Numbers to Text in Excel

Stop Excel from Changing Numbers to Dates Automatically

The only way to stop Excel from changing these numbers (or text string) into dates is by clearing letting it know that these are not numbers.

Let’s see how to do this.

Change the format to text

The easiest way to make sure Excel understands that it’s not supposed to change a number to date is by specifying the format of the cell as Text.

Since dates are stored as numbers, when you make the format of a cell text, Excel will understand that the entered number is supposed to be in the text format and not to be converted into a date.

Below is how you can stop Excel from changing numbers to dates:

1. Select the cell or range of cells where you want to make the format as Text
2. Click the Home tab
3. In the Number group, click on the dialog box launcher icon (or you can use the keyboard shortcut Control + 1).
4. In the Format Cells dialog box, in the category option, click in Text
5. Click OK

The above steps would change the cell format to text. Now when you enter any number such as 30-06-2020 or 1/2, Excel will not convert these into date format.

You can also open the Format Cells dialog box by selecting the cell, right-clicking on it and then clicking on the Format Cell option.

Note: You need to change the format before you enter the number. If you do this after the number has been entered, this would change the format to text but you would get the numeric value of the date and not the exact number/text-string you entered.

This method is best suited when you have to change the format of a range of cells. If you only have to do this for a couple of cells, it’s best to use the apostrophe method covered next

Also read: How to Convert Serial Numbers to Dates in Excel (2 Easy Ways)

Add an Apostrophe before the Number

If you only have to enter a number in a few cells and you don’t want Excel to change it to date, you can use this simple technique.

Just add an apostrophe sign before you enter the number (as shown below).

When you add an apostrophe at the very beginning, Excel considers that cell as text.

While you would not see the apostrophe sign, you can visually see that the numbers would be aligned to the left (indicating that it’s text).

By default, all numbers align to the right and all text values align to the left.

Another benefit of using the apostrophe sign is that you can still use the lookup formulas (such as VLOOKUP or MATCH) using the value in the cell. The apostrophe will be ignored by these functions.

You can also use this method to change existing dates into text. For example, if you have 30-06-2020 in a cell, you can simply add an apostrophe (‘) at the beginning and it will be changed the date to text

Sometimes, you may see a green triangle at the top-left part of the cell, which indicates that numbers have been stored as text in those cells. But since that’s exactly what we want in this case, you can ignore those green triangles, or click on it and then select the Ignore Error option to make it go away.

While these methods are great, what if you have a column full of dates that you want to use as a text and not as dates. Here are some simple methods you can use to convert date to text in Excel.

I hope you found this Excel tutorial useful!

You may also like the following Excel tutorials:

• How to Remove Cell Formatting in Excel
• How to Remove Time from Date/Timestamp in Excel
• How to Wrap Text in Excel
• Convert Time to Decimal Number in Excel (Hours, Minutes, Seconds)
• How to Stop Excel from Rounding Numbers
• How to Display Numbers as Fractions in Excel (Write Fractions in Excel)
• How to Compare Dates in Excel (Greater/Less Than, Mismatches)

4 thoughts on “How to Stop Excel from Changing Numbers to Dates Automatically”

1. john

   October 2020 at 8:51 pm

   All of the solutions which suggest changing the format to text miss the point that you can then no longer use the data for calculations. Anything pasted into excel from say a web site will change numbers to text and they can no longer be used in calculations.
   it is an annoying and ridiculous (and time consuming feature to cope with) of the latest excel software. None of the suggested solutions I have found actually work apart from time wasting manual changing.

2. Marco

   May 2020 at 11:48 pm

   These can reverse the format, but it doesn’t give the original value. How can you simply prevent Excel from doing this?

3. Juha Tontti

   March 2020 at 1:02 pm

   Entering extra spaces etc. helps if you are enetring values manually. However, there’s a bigger problem if you are importing data from a text- or .csv-file.
   I have no solution for preventing excel chancing numerical values with decimal points to dates. How can I do that??

4. jim

   January 2020 at 9:05 pm

   another way, which avoids the apostrophe, is to enter =”1/2″, then copy that and paste as values; no need to format anything – useful if you have many values to enter which can be formularised (you can’t enter a formula in a cell formatted as text)