How to Keep Leading Zeros in Excel CSV (4 Easy Ways)

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.


Download Practice Workbook


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.

how to keep leading zeros in excel csv

If we import this file into Excel, we will get the dataset like the following image.

how to keep leading zeros in excel csv

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.


4 Simple Ways to Keep Leading Zeros in Excel CSV

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. Using Cell Formatting to Keep Leading Zeros

Using Cell Formatting is one 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.

how to keep leading zeros in excel csv Using Cell Formatting to Keep the Leading Zeros

  • Now, choose Special from the Format Cells dialogue box.
  • Following that, click on ZIP Code.
  • Afterward, select OK.

how to keep leading zeros in excel csv Using Cell Formatting to Keep the Leading Zeros

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.

how to keep leading zeros in excel csv Using Cell Formatting to Keep the Leading Zeros

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. Utilizing Text Import Wizard

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.

how to keep leading zeros in excel csv Utilizing Text Import Wizard 

After that, you will see the following image on your screen.

how to keep leading zeros in excel csv Utilizing Text Import Wizard 

  • Next, click on Open.

  • Afterward, navigate to your file and select it.
  • Following that, click on Open.

how to keep leading zeros in excel csv Utilizing Text Import Wizard 

After opening your file, the Text Import Wizard dialogue box will open like the image given below.

how to keep leading zeros in excel csv Utilizing Text Import Wizard 

Step 02: Editing Text Import Wizard

  • Firstly, from the dialogue box, choose the Delimited option.
  • Subsequently, click on Next.

how to keep leading zeros in excel csv Utilizing Text Import Wizard 

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

how to keep leading zeros in excel csv Utilizing Text Import Wizard 

  • Similarly, for the Social Security Number column, choose it first.
  • Then, select the Text option.
  • Finally, click on Finish.

how to keep leading zeros in excel csv Utilizing Text Import Wizard 

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


3. Applying Power Query to Keep the Leading Zeros

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 From Text/CSV option from Get & Transform Data group.

how to keep leading zeros in excel csv Applying Power Query to Keep the Leading Zeros

  • Now, navigate to your CSV file and select it.
  • Following that, click on Import.

how to keep leading zeros in excel csv Applying Power Query to Keep the Leading Zeros

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.

how to keep leading zeros in excel csv Applying Power Query to Keep the Leading Zeros

Afterward, a Power Query Editor window will be open on your worksheet as shown in the image given below.

how to keep leading zeros in excel csv Applying Power Query to Keep the Leading Zeros

  • Now, click on the marked area in the Account Number column.

how to keep leading zeros in excel csv Applying Power Query to Keep the Leading Zeros

  • After that, select the Text option from the drop-down.

  • Subsequently, a dialogue box will open and choose Replace Current.

how to keep leading zeros in excel csv Applying Power Query to Keep the Leading Zeros

At this stage, you will be able to see that zeros in the Account Number column are back as shown in the following image.

how to keep leading zeros in excel csv Applying Power Query to Keep the Leading Zeros

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

how to keep leading zeros in excel csv Applying Power Query to Keep the Leading Zeros

There you go! You have successfully imported your CSV file into Excel and kept the leading zeros intact.

how to keep leading zeros in excel csv Applying Power Query to Keep the Leading Zeros


4. Using TEXT Function to Keep the 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.

how to keep leading zeros in excel csv Using TEXT Function to Keep the Leading Zeros

Step 02: Using Text Import Wizard

  • Firstly, select the Delimited option from the dialogue box.
  • After that, click on Next.

how to keep leading zeros in excel csv Using TEXT Function to Keep the Leading Zeros

  • Following that, check the box of Comma as marked in the image given below.
  • Afterward, select Next.

  • Now, click on Finish.

how to keep leading zeros in excel csv Using TEXT Function to Keep the Leading Zeros

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.

how to keep leading zeros in excel csv Using TEXT Function to Keep the Leading Zeros

  • By using the AutoFill feature of Excel, we can get the rest of the data as marked in the following picture.

how to keep leading zeros in excel csv Using TEXT Function to Keep the Leading Zeros

  • By following the same steps, we will get the following output.

  • Following that, click on the disc icon to save the file.

how to keep leading zeros in excel csv Using TEXT Function to Keep the Leading Zeros

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.

how to keep leading zeros in excel csv Using TEXT Function to Keep the Leading Zeros

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

how to keep leading zeros in excel csv Using TEXT Function to Keep the Leading Zeros

  • Subsequently, select Correct Acc No.
  • Next, choose the Text option as shown in the following picture.

how to keep leading zeros in excel csv Using TEXT Function to Keep the Leading Zeros

  • Similarly, select the same options for the Correct Tel No column.
  • Finally, click on Finish.

how to keep leading zeros in excel csv Using TEXT Function to Keep the Leading Zeros

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.

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, ExcelDemy. Happy learning!


Related Articles

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo