2

Yes, I've researched the question here, here, and at stackoverflow and I know this seems like a duplicate question. Therefore, I'm making my question more specific: "How can I keep leading zeroes in a comma delimited, double-quoted fields csv file when viewing in Excel 2010?"

My customer's requirements:

  1. Must be CSV file
  2. Must be able double click file to view in Excel
  3. Must keep leading zeroes, especially in the zip code field.
  4. Must double quote fields to keep comma in name field such as Donald Duck, Jr.

My attempts.

  1. If I don't double quote the fields then "=06953" works great, however Donald Duck, Jr. get split into two separate columns which is not acceptable.
  2. If, when double quoting my fields, I try "=""06953"" as this suggests, then I get ="06953" in my cell.
Community
  • 1
zundarz
  • 1,163

2 Answers2

2

Here's a solution that works on Excel 2010

  1. Double quote delimit fields that don't have leading zeros. This will allow you to use double quotes on fields that contain commas.
  2. For fields that have leading zeros, use =" for the beginning field delimiter and " for the end field delimiter. This will keep the leading zeros for that field.

It is not necessary to double quote fields with leading zeroes, when you are using double quotes as a field delimiter.

zundarz
  • 1,163
1

You miss the point about double quoting, to include double quote in field you should double it:

"=""634576345" => ="634576345

To keep leading zeroes you should present your numbers to Excel like strings so they should not be converted to numbers at all. I have no Excel installed anywhere right now but here are possible solutions that come in mind:

  • using comma instead of a point (or point instead of the comma if your locale uses comma as a decimal separator);
  • adding leading space symbol;
  • adding leading quote (') symbol (AFAIK Excel does this to distinguish ordinary strings from some-other-type-look-alike).
kworr
  • 822