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?
Asked
Active
Viewed 8.2e+01k times
8 Answers
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.
VBwhatnow
- 2,227
33
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.
Olli
- 7,739
Marvin Ward Jr
- 487
24
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 |
+----+--------------------------+-------------------------+
Nicolaesse
- 350
9
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.
Fishcake
- 352
3
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.
Giles
- 204
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".
mvark
- 2,430
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"
Rob
- 180
0
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.
mythofechelon
- 1,191