10 Simple Ways to Add Leading Zeros in Excel

Adding leading zeros in Excel can be tricky. By default, Excel removes any leading zeros from numbers. So if you type 00123, Excel changes it to 123. This happens because Excel treats numbers with leading zeros as regular numbers.

But in many cases, you need to keep the leading zeros for things like zip codes, product codes, phone numbers, etc The good news is there are a few easy ways to add leading zeros back in Excel.

In this guide I will cover the top 10 methods to add leading zeros in Excel

1. Format Cells as Text

The easiest way to keep leading zeros in Excel is to format the cells as text before entering the numbers. Here are the steps:

• Select the cells you want to add leading zeros to.
• Go to Home > Format > Format Cells.
• Under Category, select Text.
• Click OK.

Now if you type 00123 in the cell, Excel will display it as 00123. The value is stored as text, so the leading zeros are preserved.

2. Prefix with Apostrophe (‘)

Another way is to type an apostrophe (‘) before the number, like ‘00123. This converts the number to text automatically and keeps the leading zeros.

3. Custom Number Format

You can also use Custom formatting to display leading zeros in Excel. Here are the steps:

• Select the cells and press Ctrl + 1 to open the Format Cells dialog.
• Go to Number > Custom.
• Enter a format code like 000000 for 6 digits or 000 for 3 digits.
• Click OK.

This will add the leading zeros in the cell while keeping the value as a number.

4. TEXT Function

The TEXT function converts a value to text and allows adding leading zeros. Use this formula:

=TEXT(A2,"000000")

Where A2 is the cell reference and 000000 is the custom format code.

5. RIGHT Function

To add leading zeros to text, use the RIGHT function:

=RIGHT("00000"&A2,6)

Where A2 contains the text string. This will prefix zeros to make a 6 character text string.

6. REPT + LEN Functions

Another option is combining the REPT and LEN functions:

=REPT(0,6-LEN(A2))&A2

This will add the required zeros to make it a 6 digit text string.

7. Concatenate Zeros

To add a fixed number of leading zeros, use the & operator:

=0&A2

This will prefix a single zero before the value in cell A2. You can concat more zeros if needed.

8. Add/Remove Leading Zeros Tool

For bulk leading zeros addition, you can use the Add/Remove Leading Zeros tool in Ablebits Ultimate Suite.

It allows you to quickly add or remove leading zeros for the selected cells in one go.

9. Find and Replace

Use Find/Replace to add leading zeros to multiple cells easily.

• Press Ctrl + H to open the Find and Replace dialog box.
• In Find what, enter the cell reference, like A1.
• In Replace with, enter 0&A1 to prefix zeros.
• Click Replace All.

This will instantly prefix zeros to all the specified cells.

10. Flash Fill

Flash fill is another way to quickly add leading zeros to columns in Excel.

• Enter the first value with leading zeros (like 000123) in the first cell.
• Select the cell below it and start entering the next number.
• When you hit Enter, Flash Fill will automatically add the leading zeros.

It analyzes the pattern and applies it to the remaining values.

So these are the top 10 ways to add leading zeros in Excel. Adding prefixes becomes effortless once you know the right Excel tools.

I also cover a few bonus tips below:

• Remove leading zeros
• Hide leading zeros
• VBA macro to add leading zeros

Let’s go through each of these additional topics.

Remove Leading Zeros in Excel

To remove leading zeros you have added, use any of these methods:

• Change number format – If leading zeros are due to formatting, revert the format back to General.
• Convert to number – If leading zeros are there due to text formatting, convert the cells to Number format.
• Use VALUE function – If leading zeros are result of formulas, use =VALUE(A2)

This will remove the leading zeros and display the actual number.

Hide Zero Values in Excel

If you don’t want to see zeros in cells, here are two options:

• Hide zeros for entire sheet – Go to Excel Options > Advanced. Uncheck “Show a zero in cells that have zero value”.
• Hide individual zeros – Use custom number format – #;#;;@

This will display empty cells instead of zeros.

VBA Macro to Add Leading Zeros

You can also automate adding leading zeros in Excel with a VBA macro.

Here is an example code:

Sub AddLeadingZeros()    Dim cel As Range    For Each cel In Selection        cel.NumberFormat = "000000"     Next celEnd Sub

This macro loops through the selected cells and applies a 6 digit format with leading zeros.

You can customize the macro as needed.

Things to Remember!

Here are some important pointers to keep in mind when adding leading zeros in Excel:

• Text formatting preserves leading zeros but cannot be used for calculations.
• Custom format keeps leading zeros but the value remains a number.
• Formulas like TEXT convert the value to text permanently.

So use the right method based on whether you want to keep the value as text or number.

Leading zeros in Excel can be difficult initially. But after you learn these quick tricks, it becomes a breeze.