Excel: Prevent scientific notation in XLS file

[Syringer]

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]

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]

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]

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]

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.