Zahid Hasan
When importing CSV files into Excel, the leading zeros in the CSV file are frequently omitted by default. This leads to data misinterpretation in various contexts, such as account numbers, ZIP codes, phone numbers, and so on, where leading zeros convey useful information. In this article, we are going to learn 4 simple methods to keep leading zeros in Excel CSV.
Importance of Keeping Leading Zeros in Excel CSV
CSV is the acronym for Comma Separated Values. From the name, we get a basic idea about CSV. In the following picture, we have an example of a CSV file in Notepad. Here we have Personal Details of some employees of a company.
If we import this file into Excel, we will get the dataset like the following image.
Here, we can see that Excel has omitted the leading zeros by default which leads to the incorrect representation of data. That’s why keeping the leading zeros in Excel CSV is so crucial.
In this portion of the article, we will learn the detailed steps of how we can keep the leading zeros in Excel by 4 efficient methods.
Not to mention that we have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.
1. Applying Cell Formatting to Keep Leading Zeros
Using Cell Formatting is one of the easiest ways to keep leading zeros in Excel CSV. After importing your files to Excel, we will follow the steps mentioned below.
Steps:
- Firstly, select the column of ZIP Code.
- Secondly, click on the marked portion of the following picture.
After that, the Format Cells dialogue box will open in your worksheet like the following image.
- Now, choose Special from the Format Cells dialogue box.
- Following that, click on ZIP Code.
- Afterward, select OK.
Subsequently, you will see that the ZIP Code is now in its correct format.
We will follow the same steps for the column of Social Security Number.
- In the Format Cells dialogue box, choose Special.
- After that select the Social Security Number option.
- Following that, click on OK.
At this stage, you will see that all your leading zeros removed by Excel, are now restored.
Read More: [Solved]: Leading Zero Not Showing in Excel (9 Possible Solutions)
2. Using Text Import Wizard to Keep Leading Zeros
Utilizing the Text Import Wizard is another popular method to keep leading zeros in Excel CSV. Let’s follow the steps mentioned below.
Step 01: Opening File in Excel
- Firstly, go to the File tab from the Ribbon.
After that, you will see the following image on your screen.
- Next, click on Open.
After opening your file, the Text Import Wizard dialogue box will open like the image given below.
Step 02: Editing Text Import Wizard
- Firstly, from the dialogue box, choose the Delimited option.
- Subsequently, click on Next.
- After that, check the box of Comma as shown in the image given below.
- Then, select Next.
- Now, select the column of ZIP Code like the following image.
- After that, choose the Text option.
- Similarly, for the Social Security Number column, choose it first.
- Then, select the Text option.
- Finally, click on Finish.
Congratulations! You have successfully imported the Excel CSV file while keeping the leading zeros.
Read More: How to Add Leading Zeros in Excel (4 Quick Methods)
Similar Readings
- How to Add Leading Zeros to Make 10 Digits in Excel (10 Ways)
- Put 0 in Excel in Front of Numbers (5 Handy Methods)
- How to Add Leading Zeros in Excel by CONCATENATE Operation
3. Keep Leading Zeros in Excel CSV by Using Power Query
Applying the Power Query is one of the most efficient ways to keep the leading zeros in Excel CSV. Let’s familiarize ourselves with the following steps to do this.
Step 01: Importing CSV File
- Firstly, go to the Data tab from the Ribbon.
- After that, select the From Text/CSV option from Get & Transform Data group.
- Now, navigate to your CSV file and select it.
- Following that, click on Import.
After importing your CSV file, you will see a preview of your data like the following picture.
Step 02: Transforming Data
- Firstly, click on Transform Data.
Afterward, a Power Query Editor window will be open on your worksheet as shown in the image given below.
- Now, click on the marked area in the Account Number column.
- After that, select the Text option from the drop-down.
- Subsequently, a dialogue box will open, and choose Replace Current.
At this stage, you will be able to see that zeros in the Account Number column are back as shown in the following image.
- By following the same steps for the Telephone Number column, we will get the following output.
Step 03: Loading Data Back to Excel Workbook
- Now, click on Close & Load.
There you go! You have successfully imported your CSV file into Excel and kept the leading zeros intact.
4. Using Excel TEXT Function to Keep Leading Zeros
Using the TEXT function is one of the most efficient ways to keep the leading zeros in Excel CSV. Let’s walk through the steps outlined below.
Step 01: Navigating and Opening File
- By following the steps mentioned in Step 01 of the 2nd method, we can navigate and open our file and get the Text Import Wizard dialogue box.
Step 02: Using Text Import Wizard
- Firstly, select the Delimited option from the dialogue box.
- After that, click on Next.
- Following that, check the box of Comma as marked in the image given below.
- Afterward, select Next.
- Now, click on Finish.
Subsequently, your data will be imported to Excel where leading zeros are omitted.
Step 03: Using Text Function
In this step, we are going to use the TEXT function. It formats a cell into text format.
- Firstly, create 2 new columns named Correct Acc No and Correct Tel No.
- Subsequently, use the following formula in cell E5.
=TEXT(B5,"0000000")Here, B5 represents the cell of the Account Number column.
- By using the AutoFill feature of Excel, we can get the rest of the data as marked in the following picture.
- By following the same steps, we will get the following output.
- Following that, click on the disc icon to save the file.
Step 04: Reopening the File
- At this stage, close Excel.
- After that navigate and reopen the same file by following the steps mentioned in Step 01 of the 2nd method.
Step 05: Modifying Text Import Wizard Dialogue Box
- Firstly, select the Delimited option from the dialogue box.
- Afterward, click on Next.
- Following that, check the box of Tab from the dialogue box.
- Then click on Next.
- After that, select the Account Number column.
- Then, choose the Do not import column (skip) option.
- Similarly, choose the same options for the Telephone Number column.
- Subsequently, select Correct Acc No.
- Next, choose the Text option as shown in the following picture.
- Similarly, select the same options for the Correct Tel No column.
- Finally, click on Finish.
Excellent work! The leading zeros in your CSV file have been successfully preserved after being imported into Excel. Your worksheet will look like the following image.
Read More: How to Add Leading Zeros in Excel Text Format (10 Ways)
💡 Things to Remember
- While importing your data with Text Import Wizard in method 2 and method 4, keep in mind that your data needs to be in Text format.
Download Practice Workbook
Conclusion
Finally, we have to the end of the article. I sincerely hope that this article was able to guide you to keep leading zeros in Excel CSV. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel, you can visit our website. Happy learning!
