Why is Excel removing leading zeros when displaying CSV data?

Question

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.

Answer 1

Just ran into this issue. If you control the creation of the CSV file you can make the data look like the following:

"Col1","Col2"
="01",A
="2",B
="10", C

When opened up as Excel it will preserve the 01.

Answer 2

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.

Answer 3

format the column so it displays how you want it to (e.g. 00 for 2 digits)
This has the advantage it will be saved with those digits (but you would have to alter the formatting every time you edit the CSV)

Answer 4

First copy all the data / columns and rows from the actual excel sheet into another excel sheet just to be of the safe side so that you have the actual data to compare with. Steps,

1. Copy all the values in the column and paste them into a notepad.
2. Now change the column type to text in the Excel sheet (it will trim the preceding / trailing Zeros), don't worry about that.
3. Go to Notepad and copy all the values that you have pasted just now.
4. Go to your excel sheet and paste the values in same column.

If you have more than one column with 0 values then just repeat the same.

Happy Days.

Answer 5

1) Paste my numbers into an excel column (it will remove leading zeros)

2) Then right click to "format cells" custom to 0000 for a 4 digit#, 00000 for 5digits#. etc...(bring back those zeros)

3) Copy column and paste it into a .txt file. (should have all leading zeros).

4) Copy your .txt list.

5) Right click any column in excel and format it to text.

6) Paste data copied from the .txt into the excel column.(All leading zeros remain)

Delimit to your hearts desire.

Answer 6

If the damage is already done and you have the data in Excel, you can change the file format by using the custom Number Format option. Just put zeros in for number of places you want (below image will do six digits)

Here's Macro that would also do it on your entire sheet. Just specify the number of digits.

Sub whyNot()
Dim WS As Worksheet, fMat As String, p As Long

    '''''Custom Options
    Const NumberOfPlaces As Long = 3 '' change this to number of places you want
    Set WS = ActiveSheet 'or specify the sheet name with Sheets("Sheet1")


        For p = 1 To NumberOfPlaces
        fMat = fMat & "0"
        Next p

        WS.UsedRange.Cells.NumberFormat = fMat

End Sub

Answer 7

Excel has an easy solution now, to avoid automatic data conversion.

File > Options > Data > Automatic data conversion > Remove leading zeros (uncheck)