Excel – How to keep leading zeroes in a comma delimited, double-quoted fields csv file when viewing in Excel 2010

csvdouble clickmicrosoft-excel-2010

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.

Best Answer

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.

Related Question