Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
The long number 1240800388917 is copied from another cell? Or just plain text, like from Notepad? –  wilson Apr 17 '12 at 6:56

10 Answers 10

up vote 8 down vote accepted

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.

share|improve this answer
    
Yes, I see that behavior when my numeric string has more than 15 significant digits. 12345678901234567890 becomes 1.23456789012345E+19 as you say. When I click on the cell, the edit box shows the string truncated and padded with zeros as 12345678901234500000. What is odd is that it does the same thing for numbers that don't overflow excel's 15 digit limit. If I paste in 12345678901234, it is displayed as 1.23457E+13, but when I click it the edit box still shows the original string. Now, what is odd is if I format this cell to text, it still shows in scientific notation. Annoying. –  Michael Levy Apr 17 '12 at 14:39
1  
That one is a little bit of a display quirk actually - setting the format from general to text wont immediately show it as the original string, but if you go in and edit the data (as in, just click into the edit zone and click out) it'll update to be exactly what you put in. –  tanantish Apr 17 '12 at 17:27
    
If I click on the cell I can see that excel has the entire number, but regardless if I make all the cells text it still wants to use scientific notation. So frustrating! –  The Muffin Man Jun 26 '13 at 1:10

This worked for me in Excel 2010. Just select the column, right click, Format cells, Custom and choose the option that says 0 (second option below General).

share|improve this answer
    
Dude, awesome. Thanks so much for an easy fix. –  user1477388 Sep 30 at 16:37

You can format cells as Custom > #

This can be applied after you paste the data into the column, but you must do it before you save the spreadsheet.

I work a lot with barcodes (UPC's) and excel will format them as scientific notation by default. Drives me nuts! Why this isn't an option I'll never know.

share|improve this answer

This worked for me in Excel 2013, including copy-pasting the cells out to other programs. Filtering the data helped, especially for the edit steps.

  1. Select your desired cell(s).
  2. Apply the Number Format "Text". (Try Alt, H, N, then "Text".)

Then, for each of your desired cells:

  1. Enter edit mode. (Try F2.)
  2. Confirm your edit with either Enter (good for columns) or Tab (good for rows). Do not alter the contents.

Your number will appear normally, even at 100 digits. =)

share|improve this answer
  1. Copy/paste problem field into new sheet in column A

  2. Copy/paste Column A into text file

  3. Format Column B to text and paste text file into Column B

  4. In Column C:

    =IF(ISNUMBER(SEARCH("+",B1)),A1,"")
    
  5. Convert Column C into a number format with no decimals

  6. Copy/paste Column C into text file

  7. Format Column D into text and paste text file to Column D

  8. In Column E:

    =IF(IF(IF(B2=A2,A2,D2)=0,A2,IF(B2=A2,A2,D2))=0,"",IF(IF(B2=A2,A2,D2)=0,A2,IF(B2=A2,A2,D2)))
    
  9. Copy/paste Column E into notepad

  10. Delete data in problem field and format column to text

  11. Copy/paste data from text file to problem field

share|improve this answer

Excel 2010: This works one column at a time and not at all for a row. Select the column or subset of a column, from the Data tab select "Text to Columns", and jump right to "Finish." No more scientific notation.

I agree with the many Excel users, it's criminal that there is no option to prevent scientific notation!

share|improve this answer
    
In my test on Excel 2007, I had to click Next > twice, then select “Text” and click Finish. You can apply this to rows by transposing, converting, and transposing back. –  Scott Aug 14 '13 at 23:59

Try this... this works for me, Excel 2013.

=""&a1

where a1 holds the numeric value. After, copy the column and paste to original column.

share|improve this answer
    
This works in Excel 2010 as well. –  Paul G Jul 10 '13 at 14:12
1  
As a side node: ="1240800388917" does the same –  nixda Jul 25 '13 at 20:41

I found that in Office 2010, if you format the column first, and then paste in the data, it will show the large numbers correctly.

share|improve this answer
    
That is probably because it is stored as a string. What happens if you make another cell be, say, the cell's content multiplied by 2? –  Peter Mortensen Jan 11 '13 at 12:15

Select all the cells — or, to make things easy — select the entire spreadsheet and click Format Cells, then Text.

Automatically, your whole spreadsheet will use text. You don't have to do it cell by cell.

share|improve this answer

A single apostrophe ' before a number will force Excel to treat a number as text (including a default left align). And if you have errors flagged, it will show as a number stored as text error on the cell.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.