"001234" in CSV becomes "1234" in XLS

  • Thread starter Thread starter cpliu
  • Start date Start date
C

cpliu

I have CSVs with data that starts with "0" (ISBN number). Once opened with
Excel, "0" disappears because the cell is considered a number, not a text.
I have tried to enclose it with quotes but it still won't stay.

Any solutions?

Thanks for the help,


cpliu
 
Hi cpliu!

Have you tried formatting with custom format 000000

Or convert to text with a helper column and:

=TEXT(A1,"000000")

Or does it do the same if yo pre-format the range as text?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi cpliu!

Have you tried formatting with custom format 000000

Or convert to text with a helper column and:

=TEXT(A1,"000000")

Or does it do the same if yo pre-format the range as text?
Click to expand...
Thanks for the helpful information. It works as you suggested in custom
format and helper column. Just wonder how to you pre-format the range as
text on CSV file?

Thanks again for the help,


cpliu
 
Hi

The best way is to rename it from CSV to TXT. Then Excel will launch the
'Text Import Wizard which gives you a lot more flexibility over the import.

Andy.
 
Maybe you could use a custom format of "000000" after you do the Open/import.

(I use the rename to .txt and import as text if I know that I won't use the
values in arithmetic operations.)
 
Back
Top