Excel reading numbers as scientific formulas

[missy22]

Dear all,

When I download data from server side into excel, specific number such as "83437E207" gets converted into scientific number such as "8.3437E+211".

Is there a way to format excel to always to read numeric data as numbers only.

I appreciate your time and help.

Thank you

[:) Sixthsense :)]

Before pasting the data in excel format the cell / column as TEXT copy the data and Press Alt+E+S and select TEXT and give Ok.

[Pepe Le Mokko]

Also read this article on how XL stores numbers

[missy22]

Thank you for your response, I really appreciate your help.

However, there is no pasting of the data involved, as the data is automatically extracted from the server end into excel as csv format.

The data numeric data is sometimes converted into scientific formulas.

Any help to change the default settings in excel, to read numeric data as numbers only.

Thank you for your time and help.

[:) Sixthsense :)]

I believe you will be having some more file types option which you can select at the time of downloading the data from server (As text Data).

[missy22]

Dear Sixthsense,

Thank you for your reply. There is not really any file types option besides exporting data as csv.

[:) Sixthsense :)]

Two suggestions from my end

1. You ask the tech person to give access to download the file in many file types.
2. Not sure but I believe applying the Text Formatting for the Startup excel file will make every file to have cells Formatted as text - Requesting Others Suggestion

[MrShorty]

as the data is automatically extracted from the server end into excel as csv format.

Do you know specifically how the file is being imported into Excel? When using some version of the "File-open" command, Excel will take a CSV file and try to figure out what data type each field should be. When it has a choice (in this case it has to choose between "number" and "text string") it will choose "number" over "text string" when given a choice for the data type. The problem with using the "File-Open" command is that the programmer/user is not given an opportunity to override Excel's default choices.
Often when I do something like this, I will use the "Import External Data" command. When you use this command on a text file (and remember that CSV is just a text file with a different extension), you should get a "Text Import Wizard." In one of the steps of this wizard is a step where you can override the data type for a given column/field so that Excel will treat that field as text no matter what it contains.
The other option that comes readily to mind is a macro that "opens" the file and reads each field independently. If this is the case, it should be a simple job to go into the macro's code and specify that this field should be a text string rather than a number.