Reading CSV with numbers in text format, Excel automatically convertsto scientific notation


F

Fredy617

I have a CSV file with numbers like 00425067891253. They are formatted as text.
No special characters can be inserted into these numbers as they as used for batch processing and the leading zeroes must be there.

Is there any way to open these in Excel without the numbers automatically converting to scientific format?
 
Ad

Advertisements

J

joeu2004

Fredy617 said:
I have a CSV file with numbers like 00425067891253.
They are formatted as text. [....]
Is there any way to open these in Excel without the
numbers automatically converting to scientific format?

The problem is not the display in Scientific format so much. That would be
easy to remedy: simply change format to Number after opening the file.

(However, that might not be acceptable to you.)

The real problem is: Excel does not read the IDs as text if you open the
file directly in Excel. Excel reads them as numbers, even if you manually
enclose them in double-quotes.

Consequently, you "loose" the leading zeros. And worse: any numeric ID
longer than 15 digits will be modified, replacing the digits after the first
15 digits with zeros.

If all such numbers (and nothing else) are in columns, you can use the Text
Import wizard to read the CSV file into a blank worksheet instead of opening
the file directly. One of wizard options allows you to select the format of
an entire column as Text.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top