12

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.

5
  • The only way I've figured this out is arduous, but seems to work. Copy and paste to a new sheet. Set the entire pasted area to "text" format, then delete it, and paste in the values again. Bob's your aunty.
    – user3463
    Jun 1, 2012 at 22:34
  • 3
    (For our non-UK users, "Bob's your aunty" is an idiom which loosely translates to "and you're all set".)
    – iglvzx
    Jun 1, 2012 at 22:42
  • Robert was not my aunt. :-) I thought I had success doing that before, but it didn't work. Try it. What confused me is why formating the cell as text does not result in the value "02" being dispkayed with a leading 0 left justified.
    – Ivan
    Jun 2, 2012 at 0:49
  • 1
    if the csv is for excel only, you can use explicit numbers as text - e.g. if you write '01 in excel, it will display 01 without the single quote..
    – Aprillion
    Jun 5, 2012 at 8:19

7 Answers 7

6

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

enter image description here

The leading zeros will no longer be removed:

enter image description here

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.

5
  • 1
    My issue was that my file had a csv extension and the Import dialog never comes up. when I renamed the file to have a text extension, I was all set and knew where to go. ty.
    – Ivan
    Jun 2, 2012 at 0:50
  • Here's a complication: After importing it as you describe, I then want the ability to modify the file and to save it back as CSV. It presents me with a convoluted ugly dialog box (c'mon microsoft-design a custom dialog form for this instead of using the MessageBox Windows feature!). I choice the option to "keep it in the same format". When I do so, it defaults to a tab separated format. To ensure that the txt file is saved with a comma separator, you have to change the file name extension back to "csv." Is this ugly of what? After 10 versions of Excel we have this?
    – Ivan
    Jun 2, 2012 at 1:08
  • Hi Velika, see the update at the bottom of my answer. You may be better off not using Excel at all. Jun 2, 2012 at 18:33
  • Yeah, I was already looking for a CSV editor. I saw CSVEd. My initial impression is that it looks like a confusing ametuerish interface. I think "Tabular Text Editor" looks better. Thanks for your help.
    – Ivan
    Jun 3, 2012 at 22:06
17

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.

2
  • My Excel 2010 doesn't preserve 01 even with your example
    – OneWorld
    Jul 12, 2019 at 16:34
  • 1
    Thanks a lot ! I've read a lot of responses this one is simple and still works in 2021 ! Jul 5, 2021 at 7:21
1

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)

0

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.

0

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.

0

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)

enter image description here

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
0

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

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

New contributor
Peter O'Driscoll is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    2 days ago

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .