How to Keep Leading Zeros in Excel CSV Programmatically

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is a powerful program. We can perform numerous tasks on datasets using Excel tools and features. There are many default Excel functions that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Sometimes, we need to import data in excel worksheets from CSV files. But a problem arrives when we do that. The leading zeros from any cell values disappear after we import the data, which is an annoying issue. This article will show you 4 simple ways to Keep Leading Zeros in Excel CSV Programmatically.


How to Keep Leading Zeros in Excel CSV Programmatically: 4 Easy Ways

If we open the CSV files with Notepad or any other text app, we will see the leading zeros if the data contains such information. To illustrate, we’ll use a sample dataset as an example. For instance, the following CSV file contains ID, Salesman, and their Cell No. We can easily see the leading zeros in both the ID column and the Cell No. column.

how to keep leading zeros in excel csv programmatically

But, if we open the file in Excel, the leading zeros disappear. See the below picture to understand it clearly. This happens because excel assumes all the cells as General and ignores leading zeros as a result. However, this is not desired outcome. So, go through the methods in this article to know how to Keep Leading Zeros in Excel CSV Programmatically.


1. Keep Leading Zeros in Excel CSV by Transforming Data in Power Query Editor

In our first method, we’ll import the CSV file using the Power Query Editor instead of directly opening the file in Excel. Therefore, follow the steps below to perform the task.

STEPS:

  • First, go to the Data tab.
  • Then, in the Get & Transform Data section click From Text/CSV.

Keep Leading Zeros in Excel CSV by Transforming Data in Power Query Editor

  • As a result, the Import Data dialog box will pop out.
  • Select the desired CSV file and press Import.

  • Consequently, another dialog box will appear containing the dataset.
  • Here, you can see the leading zeros.
  • In case you don’t see the leading zeros, click Transform Data.

  • Now, press the icons beside the column headers from where the leading zeros got removed.
  • Next, choose Text in the context menu.

  • After that, press Close & Load.

  • Accordingly, a new worksheet will appear with the dataset where the leading zeros are present.
  • In this way, you can keep leading zeros.

Read More: How to Fix CSV File in Excel


2. Format Cell as Zip Code to Keep Zeros in Excel CSV Programmatically

Similarly, if we import the CSV file containing the Zip Codes, the leading zeros will disappear. We can easily solve the issue by formatting the cells. So, learn the following steps to carry out the operation.

STEPS:

  • Firstly, select the cell range D5:D10.

Format Cell as Zip Code to Keep Zeros in Excel CSV Programmatically

  • Then, press the Ctrl and 1 keys together.
  • Hence, the Format Cells dialog box will appear.
  • Go to the Special Category and choose ZIP Code as Type.
  • Click OK.

  • Thus, the leading zeros will appear in the zip codes.

Read More: Paste Comma Separated Values into Excel


3. Use Custom Formatting to Have Leading Zeros in Excel CSV

Moreover, we can format the cells to our requirements. In this step, we’ll show how you can custom format the cells. Hence, follow the process.

STEPS:

  • First of all, select the desired cell range.
  • Next, press Ctrl and 1 simultaneously.
  • Consequently, the Format Cells dialog box will pop out.
  • Now, choose Custom Category.
  • In the Type field, type 0 as per your requirements to keep the leading zeros.
  • Press OK.

Use Custom Formatting to Have Leading Zeros in Excel CSV

  • In such a process you’ll see the leading zeros as well.
  • Look at the result below.

Read More: How to Edit CSV File in Excel


4. Insert TEXT function in Excel for Keeping Leading Zeros Programmatically

We can also keep leading zeros by applying formulas in Excel. In our last method, we’ll insert the TEXT function to get the desired outcome. Therefore, learn the process.

STEPS:

  • In the beginning, select cell E5.
  • Then, type the formula:
=TEXT(D5, “00000”)
  • Afterward, press Enter.
  • Subsequently, use AutoFill to get the other results.
  • See the below picture where we can see the leading zeros in the new column.

Insert TEXT function in Excel for Keeping Leading Zeros Programmatically

Read More: How to Sort CSV File in Excel


Download Practice Workbook

Download the following workbook to practice by yourself.


Conclusion

Henceforth, you will be able to Keep Leading Zeros in Excel CSV Programmatically using the above-described methods. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Import CSV to Excel | Importing Data in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo