How to Keep Leading Zeros in Excel CSV – 4 Methods

Importance of Keeping Leading Zeros in Excel CSV

The following image showcases an example of a CSV file in Notepad.

If we import this file into Excel, the dataset will be displayed as shown below.

Excel omitted the leading zeros by default, which leads to an incorrect representation of the existing data.

Method 1 – Applying Cell Formatting to Keep Leading Zeros

Steps:

• Select the ZIP Code column.
• In the Home tab, click the arrow sign as shown in the image below.

The Format Cells dialog box will open.

• In Format Cells, choose Special.
• Select ZIP Code.
• Click OK.

The ZIP Code is in its correct format.

Follow the same steps for the Social Security Number column.

• In Format Cells, choose Special.
• Select Social Security Number.
• Click OK.

All leading zeros are displayed.

Read More: [Solved]: Leading Zero Not Showing in Excel

Method 2 – Using the Text Import Wizard to Keep Leading Zeros

Step 1: Opening File in Excel

• Go to the File tab on the Ribbon.

The following image will be displayed

• Click Open.

• elect your file.
• Click Open.

The Text Import Wizard dialog box will open.

Step 2: Editing Text Import Wizard

• In the dialog box, choose Delimited.
• Click Next.

• Check Comma.
• Click Next.

• Select the ZIP Code column.
• Choose Text.

• Select the Social Security Number column.
• Choose Text.
• Click Finish.

The Excel CSV file was imported, keeping the leading zeros.

Method 3 – Keep the Leading Zeros in Excel CSV by Using the Power Query

Step 1: Importing the CSV File

• Go to the Data tab on the Ribbon.
• Select Get & Transform Data and choose From Text/CSV.

• Select your CSV file.
• Click Import.

A preview is displayed.

Step 2: Transforming Data

• Click Transform Data.

The Power Query Editor window will open.

• In the Account Number column, click as shown below.

• Select Text from the drop-down menu.

• A dialog box will open. Choose Replace Current.

Zeros will be displayed in the Account Number column.

• Follow the same steps for the Telephone Number column. This is the output.

Step 3: Loading Data Back to Excel Workbook

• Click Close & Load.

Your CSV file was imported, keeping the leading zeros.

Read More: How to Add Trailing Zeros in Excel

Method 4 – Using the Excel TEXT Function to Keep Leading Zeros

Step 1: Navigating and Opening the File

• Follow Step 1 in Method 2 to open the Text Import Wizard dialog box.

Step 2: Using the Text Import Wizard

• Select Delimited.
• Click Next.

• Check Comma.
• Click Next.

• Click Finish.

Your data will be imported to Excel. Leading zeros are omitted.

Step 3: Using the Text Function

• Create 2 new columns: Correct Acc No and Correct Tel No.

• Use the following formula in E5.

B5 is the cell in the Account Number column.

• Use the AutoFill feature to drag the formula across the cells you want to fill.

This is the output.

• Click on the disc icon to save the file.

Step 4: Reopening the File

• Close Excel.
• Follow Step 1 in Method 2 to reopen the same file .

Step 5: Modifying Text Import in the Wizard Dialog Box

• Select Delimited.
• Click Next.

• Check Tab.
• Click Next.

• Select the Account Number column.
• Choose Do not import column (skip).

• Choose the same options for the Telephone Number column.

• Select Correct Acc No.
• Choose Text.

• Select the same options for the Correct Tel No column.
• Click Finish.

The leading zeros are displayed in your CSV file.

Read More: How to Keep Leading Zero in Excel Date Format

Download Practice Workbook

Related Articles

• How to Keep Leading Zeros in Excel
• How to Remove Zeros in Front of a Number in Excel
• How to Remove Leading Zeros in Excel

<< Go Back to Pad Zeros in Excel | Number Format | Learn Excel