118

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?

Improve this question
3

6 Answers 6

Reset to default
96

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.

Improve this answer
8
  • 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, 2012 at 9: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, 2012 at 10:08
  • 2
    Please note that while saving your file it goes back to same scientific notation.
    – user255598
    Sep 19, 2013 at 7:49
  • 8
    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, 2015 at 19:44
  • 3
    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, 2018 at 16:21
26

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.

Improve this answer
4
  • 3
    The only truly correct answer
    – toddmo
    May 13, 2016 at 20:42
  • This worked for me. I am trying to import with SSIS. Thank you! +1
    – Meltdown
    May 5, 2017 at 9:25
  • 1
    This is the answer worked for me.
    – Sudheer Kumar
    Jan 28, 2019 at 9:42
  • 1
    This works. Poor excel and ms...
    – Changwang Zhang
    Apr 14, 2020 at 4:13
17

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

Improve this answer
1
  • Can you override the default excel template with this so that it is kind of "permanent" ?
    – rollsch
    Jan 29, 2021 at 6:22
0

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

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

If you're pasting in a section

  1. Paste the tab-delimited data into Excel, and leave it as selected.
  2. Press the delete key. This will delete the new data, but will keep the cells selected.
  3. Set the format of the selected cells to be "text" (Ctrl+1 to bring up format menu)
  4. 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.

Improve this answer
2
  • 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, 2021 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, 2021 at 9:48
0

The only real answer is to store it as text.

  1. Paste the column in A.
  2. 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"

Improve this answer

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