Excel dropping the leading 0s

Fuggan

Fuggan

Fuggan

Associate
Joined
17 Feb 2009
Posts
2,451
Location
Leeds
Does anyone know if there is a setting in MS Excel (2013) that stops the application from automatically dropping the leading 0s from numbers when you open a .csv file?

The only way around it I have found so far is to not actually open the file but import it as text from the csv. Consulted my old pal Google and can't find any help. :(
 
If it's a number there are no leading 0s, so it has to be text, or you use a custom format to show the zeros in the cell, but for that to work you need all the numbers to be the same length.
 
As wesimmo says there's no number format that will keep leading zeros you'll have to import it as text.

Why do you want to do this?
 
Essentially, at work I deal with customer account numbers and a lot of them are entirely numeric, some of which start with a 0 or even several 0s.

When I double-click-open some .csv files with these customers in, the leading 0s get dropped and 0001123 account number becomes 1123 account (which could be another customer).

When I import the file as text and set the account number column to text, the leading 0s are retained. Just wondered if there was an option to stop them from being dropped upon open the file normally.
 
Fuggan said:
Essentially, at work I deal with customer account numbers and a lot of them are entirely numeric, some of which start with a 0 or even several 0s.

When I double-click-open some .csv files with these customers in, the leading 0s get dropped and 0001123 account number becomes 1123 account (which could be another customer).

When I import the file as text and set the account number column to text, the leading 0s are retained. Just wondered if there was an option to stop them from being dropped upon open the file normally.
Click to expand...

Is it an output from a piece of software?
if it is they need to put '' around the numbers & dates.
Its a big issue if you then need to import it into another system after looking in excel.
 
Fuggan said:
Essentially, at work I deal with customer account numbers and a lot of them are entirely numeric, some of which start with a 0 or even several 0s....
Click to expand...

They might look like numbers. But to any software these are not numbers they are text. Stop thinking of them as numbers and you'll deal with them better.
 
Make a copy of your CSV file. Change its extension to txt.

Then do a file open in excel to open the txt file. It will give the import wizard which will demonstrate to you what doing excel is doing automatically with that csv file, and what you don't want it to do.

It will also let you manually import it as text. It would a good exercise for you to do.
 
OspreyO said:
They might look like numbers. But to any software these are not numbers they are text. Stop thinking of them as numbers and you'll deal with them better.
Click to expand...

This is the crux of it, especially if you have account "numbers" that are the same but for having leading zeros.

Thinking about how Excel deals with things is key. For example if you see 6 hours in a cell, Excel is simply formatting 0.25 to show it as 6 hours, which is essentially a quarter of a day, a day is 1 in Excel.

If you continue to use numbers and format them to show leading zeros then Excel will see 0011 as being the same as 11 unless you turn them to text.

In this case text will work better, any matching or look ups will work as you want, it'll sort OK should you need to and I assume you're not performing calculations on account numbers, such as summing them, so being text won't give you any issues there.
 
I get the same with phone numbers. But splitting the area code from rest sorts it.

You may have to separate the 0000 from the rest
 
You must log in or register to reply here.
Back
Top Bottom