I have a CSV text file with the following content:
"Col1","Col2"
"01",A
"2",B
"10", C
When I open it up with Excel, it displays as shown here:
Note that Cell A2 attempts to display "01" as a number without a leading "0".
When I format rows 2 through 4 as "Text", it changes the display to
…but still the leading "0" is gone.
Is there a way to open up a CSV file in Excel and be able to see all of the leading zeros in the file by flipping some option? I do not want to have to retype '01 in every cell that should have a leading zero. Furthermore, using a leading apostrophe necessitates that the changes be saved to a XLS format when CSV is desired. My goal is simply to use Excel to view the actual content of the file as text without Excel trying to do me any formatting favors.
Best Answer
When you open the csv, you'll have the option to specify the delimiter and data type for each column. The text import wizard has 3 steps. Note that my screen shots are from Excel 2010, but this will work in exactly the same manner in Excel 2003.
Step 1: select delimited (instead of fixed width)
Step 2: select comma as your delimiter
Step 3: select each column and change the data format to "text". (You will see the word Text displayed above each column as in the screen shot below.)
The leading zeros will no longer be removed:
UPDATE: If you don't want to navigate through the process to get Excel to cooperate, you can use CSVEd, an editor designed specifically for editing delimited text files. A good text editor like Notepad++ might also work, but you won't be able to see your data arranged in columns.