"001234" in CSV becomes "1234" in XLS

[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

 

[Norman Harker]

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.

 

[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?

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

 

[Andy B]

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.

 

[Allen Hart]

CPLIU;

Put an apostrophe in front of the string

Allen Hart

 

[Dave Peterson]

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.)