Turn off scientific notation in Excel

Question

I would like to prevent Excel behavior that any large number I enter is stored as a number, then abbreviated to scientific notation. Is it possible to tell Excel 2007 to stop doing this?

Possibly relevant if you're importing a file: How can you make Excel 2007 stop formatting large numbers as scientific notation? — Bob, Jul 24 '12 at 09:59
Another possibly relevant question: Why does Excel treat long numeric strings as scientific notation even after changing cell format to text — Bob, Jul 24 '12 at 10:00
And if you actually need to work with large numbers, there's an add-in, as mentioned an answer to in this question: Why is Excel truncating my 16-digit numbers? — Bob, Jul 24 '12 at 10:04
The best option to workaroung that sht is to use Google Sheets**. — serge, Feb 24 '23 at 23:38

VBwhatnow · Accepted Answer · 2012-07-24T09:39:45.513

107

Unfortunately excel does not allow you to turn this functionality off by default.

However if you select your data, right click, and click "Format cells..." and choose Number you can stop excel from changing your data to scientific notation.

edited Jul 24 '12 at 09:39

answered Jul 24 '12 at 09:22

VBwhatnow

2,219

4

I would argue that your modification to the question changes its meaning. Excel only accepts numerical data up to a certain precision, so there's a distinction between storing as text (essentially unlimited precision, but difficult to manipulate) and storing as a number. The original question did ask how to store as text by default. And then there's how the (imprecise?) number is displayed, which the modified question and your answer address. – Bob Jul 24 '12 at 09:56
1

You are right when you say that if the asker wants to actually store as text my answer would be incorrect. He can clarify later if my answer is not satisfactory and we can look at other solutions. – VBwhatnow Jul 24 '12 at 10:08
2

Please note that while saving your file it goes back to same scientific notation. – Sep 19 '13 at 07:49
12

Keep in mind however that if there are more than 15 digits in the number excel will convert the digits at the end to "0"
Seriously... it will do that...

https://support.microsoft.com/en-us/kb/269370
– theFisher86 Dec 17 '15 at 19:44
This worked perfectly in Excel 2016 for Mac - set display format to numbers, then Save As... and choose Text File (tab delimited) *.txt or whatever, and it saved without exponential formatting. – Demis Nov 29 '16 at 01:39
4

The issue is that excel loses the data when you open a csv file. It cannot be stopped from truncating really long numbers such as 20 digit SIM card ICCIDs. – Justin Goldberg Aug 16 '18 at 16:21
1

Select the Cell, format it as Text, press F2 key and then Return key. – Matias Masso Dec 02 '21 at 19:49
I really do not enjoy working with Excel... – Giacomo Feb 16 '22 at 11:58
After 35 years of Excel, and you still can't export to CSV correctly by default. Issues to keep in mind (Excel 365): (1) Numbers larger than some N digits will either be truncated or (b) removing ending 0s. (c) Selecting cell and format as text is not doing the same as if you select multiple cells and formatting as text, which has no effect on large numbers, and insists displaying in scientific notation. Absurd! – not2qubit Mar 10 '23 at 10:05

score 30 · Answer 2 · edited Feb 24 '14 at 14:29

30

This is inconvenient (yet effective) hack. Note, this is only really useful if you need to import the full number into another application (like Access) or provide the proper display.

If you format the column in question as number, you can copy all correctly formatted numbers into Notepad. Then just create a new column formatted as text, and copy the Notepad representation into the new column.

edited Feb 24 '14 at 14:29

Olli

7,631

answered Feb 24 '14 at 13:32

Marvin Ward Jr

457

4

The only truly correct answer – toddmo May 13 '16 at 20:42
This worked for me. I am trying to import with SSIS. Thank you! +1 – Meltdown May 05 '17 at 09:25
1

This is the answer worked for me. – Sudheer Kumar Jan 28 '19 at 09:42
1

This works. Poor excel and ms... – Changwang Zhang Apr 14 '20 at 04:13
It looks long winded, but only takes a few seconds and works exactly as described. Worked for me after I've tackled this problem so many times, and nothing else worked. I'm dealing with tables of ICCID numbers on SIM cards, eg '89610180000676404041'. In the modern tech world large integers are commonplace. Excel seems to be stuck in the 1990s. – Stephen Hosking Dec 01 '23 at 06:11

score 21 · Answer 3 · answered Mar 08 '18 at 13:30

Maybe scientific notation is one of the less useful Excel behaviour.

If you have a scientific notation in the A1 cell you cant just use this formula to have a text formatted value in the B1 cell:

=CONCATENATE(A1)

This is an example of the results:

+----+--------------------------+-------------------------+
|    |            A             |            B            |
+----+--------------------------+-------------------------+
|  1 | Ugly scientific notation | Awesome numbers as text |
|  2 | 8,80001E+11              | 880001465055            |
|  3 | 8,80001E+11              | 880001445410            |
|  4 | 8,80001E+11              | 880001455678            |
|  5 | 8,80001E+11              | 880001441264            |
|  6 | 8,80001E+11              | 880001413012            |
|  7 | 8,80001E+11              | 880001391343            |
|  8 | 8,80001E+11              | 880001373652            |
|  9 | 8,80001E+11              | 880001273395            |
| 10 | 8,80001E+11              | 880001115228            |
| 11 | 8,80001E+11              | 880001185106            |
| 12 | 8,80001E+11              | 880001062936            |
| 13 | 8,80001E+11              | 880000796522            |
| 14 | 8,80001E+11              | 880000890200            |
| 15 | 8,80001E+11              | 880000596278            |
| 16 | 8,8E+11                  | 880000243378            |
| 17 | 8,8E+11                  | 880000069050            |
| 18 | 8,85101E+11              | 885100625390            |
+----+--------------------------+-------------------------+

=TEXT(A1, "0") also works, as does probably a billion other formulas — drzaus, Jun 21 '18 at 15:17
This is a lot like the notepad answer above, but doesn't require an external application. I like it for that reason. — Mike_K, Jun 27 '20 at 17:19
It's we're comparing formulaic methods, I would argue =A1&"" is the shortest, therefore best. — Cameron Critchlow, Dec 13 '22 at 22:57

score 2 · Answer 4 · answered May 13 '20 at 22:16

2

One workaround I use when pasting in tab-delimited data that contains long numeric codes is to set the cell format before I paste the data.

If you're pasting into a blank sheet

Select all (Ctrl+A).
Format the cells as "text" (Ctrl+1 to bring up the format menu).
Paste your data (Ctrl+V).

If you're pasting in a section

Paste the tab-delimited data into Excel, and leave it as selected.
Press the delete key. This will delete the new data, but will keep the cells selected.
Set the format of the selected cells to be "text" (Ctrl+1 to bring up format menu)
Paste the new data in again

It seems that Excel will leave the numeric codes alone if the cells are already set as text, but will otherwise treat them irreversibly as numbers.

answered May 13 '20 at 22:16

Giles

194

This doesn't work in many cases. Excel in office 365 seems to be changing the format. You can select the whole column, change to Text, copy in a long number, and the format is switched automatically to General. – Rob Jun 11 '21 at 15:00
@Rob Yes, Office 365/Microsoft 365 is a different product in many ways, from Microsoft Office, reference in the question. I don't use it myself, but I'm sure it has functional differences. – Giles Jun 14 '21 at 09:48
does not work, even if you put format cell as text, is does convert to scientific notation – serge Feb 24 '23 at 17:48
@serge, which version of Excel are you using? This question is about the desktop application, not the Office 365 web application. – Giles Feb 27 '23 at 20:34
@Giles, the MS Excel for Microsoft 365 MSO version 2301, is a desktop application – serge Mar 01 '23 at 12:28

score 2 · Answer 5 · answered Jul 24 '12 at 10:19

2

If you want to make ALL numbers typed within a sheet NOT to appear in scientific format, you can consider creating a template & reusing it.

When you create the template, select the whole sheet and set the default number format for all cells to "Number".

answered Jul 24 '12 at 10:19

mvark

2,432

Can you override the default excel template with this so that it is kind of "permanent" ? – rollsch Jan 29 '21 at 06:22

score 0 · Answer 6 · answered Jun 11 '21 at 15:01

0

The only real answer is to store it as text.

Paste the column in A.
In B, =""&A1&""(two double quotes)

(adds quotes to it, forcing text)

If it's a CSV, store the number as quotes beforehand.

"1234", "1234"

answered Jun 11 '21 at 15:01

Rob

180

1

That doesn't work for CSV files. Even if it is in quotes, it will still treat it as a number. – Jonathan Allen Jul 26 '23 at 00:13

score 0 · Answer 7 · answered Jan 04 '24 at 15:11

It looks like this is being rolled out gradually: https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel

I had the following prompt once, but the functionality and option have disappeared since:

By default, Excel will perform the following data conversions in this file:

• Convert large numbers into scientific notation

Do you want to permanently keep these conversions?

[ ] Don't notify me about default conversions in .csv or similar files.

score 0 · Answer 8 · answered Feb 29 '24 at 13:44

0

This appears to have finally been resolved. Posting this here in case others find this 12 year old question as I did.

If you go to Options -> Data you will find a section enabling you to control what automatic conversions are performed.

answered Feb 29 '24 at 13:44

Fishcake

263

Turn off scientific notation in Excel

8 Answers8

If you're pasting into a blank sheet

If you're pasting in a section

Linked

Related