Excel – does Excel treat long numeric strings as scientific notation even after changing cell format to text

cell formatmicrosoft excelmicrosoft-excel-2010

I'm trying to process some data in Excel. The data includes numeric account numbers and other long numeric strings (like cell phone MEIDs). I am not doing math operations on these strings, so I want Excel to treat them as plain text.

Here is what is making me nuts (this is Excel 2010):

  1. Take a long number like 1240800388917 and paste it in a cell in a new worksheet.
  2. Excel's default cell format is general, so the string is presented in scientific notation as 1.2408E+12
  3. Right click on the cell, select Format Cells, set the format to Text

The cell is still displayed in scientific notation, even though the format has been set to text.

Now, if I do the steps in a different order:

  1. Format an empty cell as text. Right click on the cell, select Format Cells, set the format to Text
  2. Take a long number like 1240800388917 and paste it in to the text formatted cell

Now, the cell is displayed as a string and not in scientific notation.

The results remaining in scientific notation even though the cell is formatted as text just seems broken to me. I've seen suggested work-arounds like: use CSV import and set the format to text, add a space character to the beginning of each numeric string, and others.

Is there a simple good work around to easily keep these strings formatted as text?

Why on earth does Excel do this?

Related SU questions I found:
How can you make Excel 2007 stop formatting large numbers as scientific notation? and
Is this Excel behaviour with a large hex number expected?

Best Answer

Ah, memories of data munging back from when I did some massive number cross checking in Excel (never again).. When you punch in long numeric strings into Excel, try say, 12345678901234567890 (20 digits), Excel will generally convert it for you, meaning that the 20 digit number you've just tapped in has been cut back to be only about fifteen significant figures.

Note as well: the conversion is a truncation, as opposed to rounding. (12345678901234567890 is 1.23456789012346E+19, but excel will show you 1.23456789012345E+19)

This occurs at point of entry, so once it's in, any additional detail is lost, and so even if you tell Excel that you really meant that was text, and not a number, you're kind of out of luck, and hence why your first sequence doesn't work.

Related Question