Excel: Prevent scientific notation in XLS file

Syringer

Lifer
Aug 2, 2001
19,333
2
71
I love Excel and use it more than anything else everyday, but the way it handles large numbers is absolutely annoying.

With .txt files the easiest work-around is to to a File, Open which prompts you to specify how to treat a column (e.g. as a Text or number)...and with .csv's I can do that too by importing the data.

However once an XLS is saved there's no option to specify how to open each column, and even if I format it as "text", it'll still display in scientific notation unless I manually edit each one and hit Enter, which actually converts it to text.

Any work arounds this??
 

radhak

Senior member
Aug 10, 2011
843
14
81
I'm sure I'm missing something here - what did you mean by 'still display in scientific notation'?

I tested with Excel 2007, and numbers formatted as 'text' stayed text, while other formatted as Scientific stayed that way when the xlsx file was reopened.

Of course, the default format is auto-detected by excel to be number, and those also stay as is.
 

Syringer

Lifer
Aug 2, 2001
19,333
2
71
Try this...

a) Type in long numeric strings into a few cells (without changing the format)
b) Observe it turn into sci-fi
c) Save, close, re-open
d) Try to get those numbers to display as Text.

If we were in charge of inputting the data originally this wouldn't be an issue, since we can format as Text to begin with--but if we receieve data from a client that's in "general" form and go to sci-fi to start with, once it's saved and sent to us it's hard to "convert" it to text form.
 

radhak

Senior member
Aug 10, 2011
843
14
81
I see the problem now.

I can understand you don't want it in the scientific, but why do you need it in text format? A 'number' format (with the required decimal points, or none) expands the number to its full length - is that fine? Or, you could use a custom format (use a lot of #s) to get the same effect : a string of numbers. You could left-align if you want. Applying to the whole document is easy enough too.
 

Qbah

Diamond Member
Oct 18, 2005
3,754
10
81
What I do:

1. Open a new .xls document
2. Select all cells, right click, Format Cells, Category: Text, OK
3. Paste your stuff from the .txt file - it will paste correctly

The behavior is very annoying though.