72

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?

56

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.

  • 3
    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 9:56
  • 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. – user255598 Sep 19 '13 at 7:49
  • 3
    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... support.microsoft.com/en-us/kb/269370 – theFisher86 Dec 17 '15 at 19:44
  • 1
    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
11

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.

  • 1
    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 5 '17 at 9:25
  • This is the answer worked for me. – Sudheer Kumar Jan 28 at 9:42
5

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            |
+----+--------------------------+-------------------------+
  • 1
    =TEXT(A1, "0") also works, as does probably a billion other formulas – drzaus Jun 21 '18 at 15:17
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".

protected by Community Mar 13 '14 at 0:09

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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