Keep Leading Zeroes in Excel CSV

By default, Excel removes leading zeroes from any real number you enter in a cell. This is because the zeroes are insignificant. Sometimes you may want to keep the leading zeroes as in the case of phone numbers, credit card numbers, product codes, and so on. This tutorial shows you how to keep leading zeroes in an Excel CSV file.

How to Keep Leading Zeroes When Entering Data

We will use the following dataset in our examples:

Method 1: Apply the Text Format Before Entering the Data

Suppose we want to enter product codes that have leading zeroes in the range B2:B4.

We use the steps below:

1. Select range B2:B4.

2. Right-click the selection and click Format Cells on the shortcut menu.

Note: You can also open the Format Cells dialog box by pressing Ctrl + 1.

3. In the Format Cells dialog box, select the Text category in the Number tab and click OK.

The Text format is applied to the selection.

Note: You can also apply the Text format to the selection by clicking Home >> Number >> Number Format >> Text.

4. Type in the product codes with leading zeroes.

Excel does not remove the leading zeroes.

Method 2: Begin with the Apostrophe During Data Entry

The fastest way to apply a Text format to a cell and keep the leading zeroes during data entry is to use an apostrophe.

We use the steps below:

1. Select cell B2.
2. First, insert an apostrophe (‘) and then type in the product code with leading zeroes.

3. Press the Enter key on the keyboard or click the Enter button on the Formula Bar.

4. Repeat steps 2 and 3 as you type in the other product codes.

Method 3: Apply a Custom Number Format

We use the following steps:

1. Select range B2:B4.
2. Press Ctrl + 1 to open the Format Cells dialog box.
3. In the Format Cells dialog box, select the Text category in the Number tab.

4. Delete the format that is in the Type text box on the right of the Category box and type the format “000”# in the box and click OK.

Note: The zeroes with double quotation marks(“000”) ensure that Excel keeps the leading three zeroes. The hash (#) mark defines any number of digits that you want to follow the leading zeroes.

5. Type the product codes with leading zeroes in the range B2:B4.

Excel does not remove the leading zeroes in the product codes.

Method 4: Use the CONCATENATE Function

The CONCATENATE function joins several strings into a single string.

We use the following dataset in illustrating this method:

We use the steps below:

1. Select cell C2 and type in the following formula:

=CONCATENATE(“000”,B2)

2. Press Enter on the keyboard or click the Enter button on the Formula Bar.

3. Double-click or drag down the fill handle to copy the formula down the column.

Method 5: Use Hyphens in Between Numbers

We use the following dataset in our example:

We use the steps below:

1. Select cell B2 and type in the following product code:

000-512

2. Type in the other product codes using the same pattern.

How to Keep Leading Zeroes When Importing Data

Suppose you want to import data that has leading zeroes into your Excel CSV file. You can use Power Query to convert numbers to text when you import the data.

We use the following steps:

1. Click Data >> Get & Transform Data >> Get Data >> From File >> From Text/CSV.

2. Navigate to where the Text/CSV file is stored. Select it and click Import.

3. Click the Transform Data button at the bottom of the Power Query window to load the Query Editor.

4. Click on the column header of the Product Code column to select the column.

5. Click Home >> Transform >> Data Type >> Text.

6. Click the Replace Current button in the Change Column Type dialog box that appears.

The data in the column is changed to text and the leading zeroes appear.

1. Click the Close & Load button to finish the importation process.

The data is imported into the worksheet in form of a table.

Conclusion

Excel by default removes leading zeroes from any real number you enter in a cell because the zeroes are insignificant. Sometimes however you may want to keep the leading zeroes. This tutorial looked at six methods that one can use to keep the leading zeroes in an Excel CSV file.