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.
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.
- First, go to the Data tab.
- Then, in the Get & Transform Data section click From Text/CSV.
- 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.
- Firstly, select the cell range D5:D10.
- 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.
- 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.
- 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.
- In the beginning, select cell E5.
- Then, type the formula:
- 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.
Read More: How to Sort CSV File in Excel
Download Practice Workbook
Download the following workbook to practice by yourself.
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.