168

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?

1
  • The long number 1240800388917 is copied from another cell? Or just plain text, like from Notepad?
    – wilson
    Apr 17, 2012 at 6:56

14 Answers 14

32

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.

8
  • 1
    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. Apr 17, 2012 at 14:39
  • 8
    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, 2012 at 17:27
  • 9
    So you've reminisced and reiterated the problem. Now, how do you fix it? Nov 25, 2018 at 22:27
  • 2
    False. It does not happen at the point of entry. Otherwise the answer below would not work. Apr 26, 2022 at 6:25
  • 2
    This "answer" should not be accepted when there are actual solutions, such as changing the format to "General > 0"
    – Bort
    Oct 20, 2023 at 14:38
240

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).

11
  • 35
    This is so much better than the accepted answer which says "you're kind of out of luck"! I had a CSV file which had this problem, and the custom formatting solution worked perfectly.
    – davidair
    Apr 17, 2015 at 19:48
  • 27
    This still appears to truncate in my test. Using Excel 2013 I entered the following into a cell 12345678901234567890. When I hit enter this is displayed as 1.23457E+19 and the underlying value is changed to 12345678901234500000 so there is data loss. This doesn't answer the original question. Oct 21, 2015 at 22:31
  • 4
    Here I am a year later revisiting the same answer and wishing I could upvote it again. Jan 26, 2017 at 6:05
  • 15
    This answer fixes the formatting, but does not preserve the entered data. That is why I cannot accept it as a correct answer. I posted a short video to show. When you follow these directions, and then enter a long integer like 12345678901234567890 you lose precision when treating it as numeric, even if it is displayed in non-scientific notation. Excel treats it as the value 12345678901234500000. Only by treating it as text can you maintain all the digits. See youtu.be/7EDcR7TQpYs Sep 22, 2018 at 22:34
  • 1
    This seems to only work for me if I do this to the column prior to pasting in vals.
    – twk
    Sep 30, 2019 at 18:48
18

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

=""&a1

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

3
  • This works in Excel 2010 as well.
    – Paul G
    Jul 10, 2013 at 14:12
  • 3
    As a side node: ="1240800388917" does the same
    – nixda
    Jul 25, 2013 at 20:41
  • 1
    This is the correct answer! Also, I cannot believe this behaviour in Excel hasn't been fixed yet, it's absolutely dumb. Apr 19, 2023 at 12:29
16

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.

4
  • 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? Jan 11, 2013 at 12:15
  • 1
    This works in Excel 2016/Office 365. I selected all cells, formatted them to Text and then pasted my copied data and it did not show up with the annoying scientific notation. Thanks!
    – frezq
    Jun 12, 2017 at 8:18
  • This what also worked for me in Google Sheets... "Format > Number > 0" prior to pasting in vals and didn't convert to scientific notation while also NOT losing end values (rounding down of vals), which was happening too often.
    – twk
    Sep 30, 2019 at 18:48
  • Note you must format the cells as "Text". Formatting them as "Custom 0" will cause the leading 0s to be stripped.
    – Josh Noe
    Apr 20, 2022 at 19:20
14

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.

4
  • This is not always the case. Excel has sometimes munged my data even with a leading apostrophe.
    – Cerin
    May 11, 2015 at 20:50
  • 3
    Cerin, a fair point-never underestimate Excel (or Microsoft's) ability to do something completely unexplainable or repeatable. However, the apostrophe trick generally works as advertised.
    – dav
    May 11, 2015 at 23:07
  • 1
    can you do this in bulk? I have data with 60000 cells that I want to convert to text. Apr 8, 2018 at 15:44
  • Or just choose to format as text in right-click, Format Cells menu ;)
    – Shayan
    May 8, 2019 at 14:08
11

I'm aware of the age of the question, but this is the one I've landed after google search and which did not answered my question (why formatting as text doesn't work and why number is cut down to 15 digits after format to text).

Short answer is: you can't work on your number the way you like AFTER you entered the numeric value and tapped Enter. The moment you do that the number will be truncated to 15 digits and presented in as exponential.

This is not a bug, it's a feature.

Why is it happening is rather simple and this here answer delivers it nicely.

So what happens is that Excel autodetects type of input and if it's a number then it has precision limit of 15 digits and, if cell is formatted with 'General' format, it's displayed as exponential if value has 12 digits or more.

I believe it has to do with shell integration of Excel with '.CSV' files - in order for Excel to open them properly, assumptions have to be made. It is how Excel works for as long as I'm using it - that is: since 2001 at least.

Anything you do after that will be using the truncated value, so no formatting trick will help. However, you can use the Text to columns option to convert exponential notation to text (Click on column header, click Text to data, then Delimited, Next, untick all delimiter boxes, Select Text in Column data format and then Finish) to have the values converted to text and immediately displayed as 15 digit number. But, it will be only 15 digits, so if you had longer number, rest is "lost" to precision limit.

In order to have the number in the spreadsheet exactly the way you typed it in you have to store it as text. So you have two ways of doing that, but they will work only BEFORE/DURING editing:

  1. you need to precede it with apostrophe or format cells as text before typing/copying values into it,

  2. you have to format the target column as Text before input.

If you're using method 1 and it "sometimes doesn't work", then, sorry, you either do something wrong or you have some sort of autocorrect on (if you use apostrophe and large number >15 digits Excel treats it as a wrong value and flags cell with warning message, so this remark is not personal nor critique).

Method 2 is very useful, because it works with copy&paste, even with large datasets.

Other way to do it in bulk numbers is to import values from text file. I dare say it's the only way for most situations. And be sure to import the file, not just open it, as Excel treats csv type like it's native format and trims large numbers to 15 digits as a matter of course.

Now, to the last issue. If you enter large numerical value in Excel cell formatted as text it will still be displayed in scientific notation as long as the cell is not wide enough. It's annoying, but side effect of some Excel internal algorithms. Just make the cell wider and you'll see full value every time.

7
  • A side note: Text to columns is in the ribbon menu under Data.
    – Culip
    Jan 30, 2020 at 16:17
  • 6
    I'd say it's a bug considering the behavior is maddening and makes no sense to a human. I have NEVER wanted a long integer converted to e-notation. Feb 27, 2020 at 23:05
  • 1
    @AcePL: So where exactly do you explain that this insane behavior is a feature?
    – David.P
    May 7, 2023 at 22:16
  • @David.P - well spotred, edited to answer that.
    – AcePL
    May 8, 2023 at 23:57
  • 1
    At least there should be an option to turn off that "Feature" and to allow long numbers to remain text.
    – user890332
    Aug 15, 2023 at 19:10
4

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!

1
  • 1
    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. Aug 14, 2013 at 23:59
4

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.

1
  • That doesn't transform the numbers to text though, which can cause problems later on. But well...
    – Joris Meys
    Jan 11, 2016 at 9:44
3

Excel is frustrating for CSV/bulk data work like this. Work with LibreOffice if possible, it doesn't do this, that is, if you open the same sheet (.xlsx) in Libreoffice, the large 'numbers' which are actually strings are not displayed in scientific notation (which is an absurd decision by Excel). LibreOffice's file fidelity is great: open the same .xlsx in Excel again, and the scientific notation instantly "works" just like usual.

1

In Excel 2013, the same thing happened to me and I just went to Format Cells -> Number then made sure the decimal place is set to "0".

1
  • 1
    similar to Anonymous answer above but selecting "Number" somehow makes more sense than selecting "custom".. plus it's higher on the list. anyway, it does the same thing but I like this better Feb 9, 2016 at 1:55
1

As mentioned in other posts, Excel overwrites the data you pasted with the scientific notation, so there's no way to go back no matter what you change the format to.

The way I deal with this is I always first paste all the data into excel and note all the columns that have scientific notation. Then I undo the paste, highlight all the columns I noted before (Ctrl-Click) and change format to text. After that I re-paste and all the scientific notations disappear.

0

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.

2
  • 1
    Alas, this doesn't work. The text will just be in scientific notation, that's the whole problem...
    – Joris Meys
    Jan 11, 2016 at 9:44
  • The OP meant to select all the cells and set to text BEFORE copy pasting in your values.
    – leerssej
    Nov 2, 2023 at 21:37
0
  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

0

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. =)

1
  • Does not work for multiple cells (Excel 365)
    – Albin
    Apr 27, 2019 at 8:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .