When using CSV files you may find that the CSV’s are losing leading zeros when you open them. This is happening because Excel is assuming that if it is a number, it cannot have leading zeros so it just removes them. However, leading zeros in ‘numbers’ are often there because the ‘number’ is actually something different e.g. a bank account number which may well have leading zeros.
Table of contents
YouTube CSV losing leading zeros and changing number
Watch, like, subscribe
Don’t let Excel open the CSV automatically
The first thing to be careful of is that on most computers the default setting to handle CSV files is to use Excel to open them (when you double click the file it just opens with Excel and doesn’t ask any questions)
To stop this happening you must rather open the file from within Excel using the Text Import wizard. In newer versions of Excel you may need to switch the Text Import wizard back on.
The reason we want to use this is because through this method you can tell Excel to NOT treat a column as a number but rather treat it as text. This will force Excel to keep the leading zeros and also not change any ‘numbers’ with more than 15 characters.
As per below, in a blank spreadsheet, click on the DATA ribbon, then GET DATA, then LEGACY WIZARDS, then FROM TEXT LEGACY
Stop CSV losing leading zeros
If you followed the above steps the next screen will look something like this. Note that there are leading zeros in the data (bottom red arrow) which we need to keep. As this is a CSV we have made sure that the ‘Delimited’ is chosen.
The next screen is self explanatory. Choose what the delimiter is (in this case comma but it could be semicolon depending on your computer settings). Click next
The screen below is the most important. By default Excel will use the ‘General’ format to format the columns. With ‘General’ Excel is allowed to choose the best fit for the data it sees, so when it sees lots of numbers it treats them like numbers, and leading zeros are not removed.
To stop this you need to:
- Click on the column that you want to control (it should be highlighted in black), then
- Change the ‘Column data format’ to rather be ‘Text’
Now when you click ‘Finish’ Excel will ignore its natural impulse to treat that column as a number and rather treat it as text (which means every character is kept- no dropped zeros or big numbers losing the exact numbers at the end).
Related
Online Excel course covering leading zeros in Excel
Entering Long Numbers in Excel
Using multiple characters as delimiters in Excel Text to Column