If you have a CSV file, you will always lose any leading zeroes when you open the file in Excel. This causes problems with zip codes and social security numbers. Episode 481 shows the non-Excel method for solving this problem.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Well come back to the MrExcel netcast.
I'm Bill Jelen.
Today we have a question that was asked in one of my Excel seminars.
I thought this was a great question, the person had a text file that was coming in from somewhere and it was actually a CSV file a comma separated values stored with an extension of CSV and the person said that they had a problem where the leading zeros on ZIP codes and Social Security numbers were getting lost.
Now, of course the solution to this occurs in step 3 of the Text to Columns wizard, but unfortunately, we have a problem because this particular file is a CSV file, we never get to see step 3 of the wizard. Here's the file in notepad you can see that we have some Social Security numbers here that start with zero, a few ZIP codes that start with zero.
Now, let's take a look at what happens when we open this file with Microsoft Excel because it's a CSV file, Excel automatically opens the file without taking us through the Text to Columns wizard and you'll see that we lost the leading zeros on all of those numbers and the leading zeros on the ZIP codes.
We need to find a way to force Excel to take us through the text to Columns wizard.
Let me close this file, and we'll go back to Windows Explorer.
Now, in my Windows Explorer.
I've gone in and changed the settings. I've got into Tools, Folder Options and under View, about a third of the way down. There's a setting here called Hide extensions for known file types.
Now, by default that's turned on I've turned mine off.
So, that way I can actually see the extension and now I can see aData.CSV the trick to solve this problem is to go through rename the file.
So, I'm going to rename and change it to a .txt file.
So, I changed it from a CSV file to a txt file.
It warns us and says if you change the filename extension it may become unusable.
Well, that's okay, because we're just changing from CSV to txt now if we go back to Excel we use File open, we say we want to see all the text files instead of opening adata.CSV we open the txt file click open and now because there's a .txt extension it takes us through the wizard.
So, we can say that it's Delimited, in step 2 it says Delimited by a Comma and then in step 3 this is the important part will say that this field the Social Security number is text and the ZIP code field is text, that forces the leading zeros to stay put.
Now, of course I hate using that text option, but in this case we have no choice in order to keep those leading zeroes the big problem we have is that this column is now, I'll call poison.
We can't insert any new formula.
So, if I come here, =2+2, we get the text instead of the formula you actually have to go through and reformat those columns as general.
If you need to be able to later and our formulas or totals, of course no reason to enter totals and the Social Security number or ZIP code so, we don't have a problem there.
But it's a great point if you have a file that stored as a .CSV file, you never get an opportunity in the third step of the wizard because Excel just automatically opens the file.
The solution go out to Windows Explorer rename the file to be a .txt file, open in Excel where we can then have some control over the settings.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today we have a question that was asked in one of my Excel seminars.
I thought this was a great question, the person had a text file that was coming in from somewhere and it was actually a CSV file a comma separated values stored with an extension of CSV and the person said that they had a problem where the leading zeros on ZIP codes and Social Security numbers were getting lost.
Now, of course the solution to this occurs in step 3 of the Text to Columns wizard, but unfortunately, we have a problem because this particular file is a CSV file, we never get to see step 3 of the wizard. Here's the file in notepad you can see that we have some Social Security numbers here that start with zero, a few ZIP codes that start with zero.
Now, let's take a look at what happens when we open this file with Microsoft Excel because it's a CSV file, Excel automatically opens the file without taking us through the Text to Columns wizard and you'll see that we lost the leading zeros on all of those numbers and the leading zeros on the ZIP codes.
We need to find a way to force Excel to take us through the text to Columns wizard.
Let me close this file, and we'll go back to Windows Explorer.
Now, in my Windows Explorer.
I've gone in and changed the settings. I've got into Tools, Folder Options and under View, about a third of the way down. There's a setting here called Hide extensions for known file types.
Now, by default that's turned on I've turned mine off.
So, that way I can actually see the extension and now I can see aData.CSV the trick to solve this problem is to go through rename the file.
So, I'm going to rename and change it to a .txt file.
So, I changed it from a CSV file to a txt file.
It warns us and says if you change the filename extension it may become unusable.
Well, that's okay, because we're just changing from CSV to txt now if we go back to Excel we use File open, we say we want to see all the text files instead of opening adata.CSV we open the txt file click open and now because there's a .txt extension it takes us through the wizard.
So, we can say that it's Delimited, in step 2 it says Delimited by a Comma and then in step 3 this is the important part will say that this field the Social Security number is text and the ZIP code field is text, that forces the leading zeros to stay put.
Now, of course I hate using that text option, but in this case we have no choice in order to keep those leading zeroes the big problem we have is that this column is now, I'll call poison.
We can't insert any new formula.
So, if I come here, =2+2, we get the text instead of the formula you actually have to go through and reformat those columns as general.
If you need to be able to later and our formulas or totals, of course no reason to enter totals and the Social Security number or ZIP code so, we don't have a problem there.
But it's a great point if you have a file that stored as a .CSV file, you never get an opportunity in the third step of the wizard because Excel just automatically opens the file.
The solution go out to Windows Explorer rename the file to be a .txt file, open in Excel where we can then have some control over the settings.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
