How To Remove and Add Leading Zeros in Excel

Leading zeros in Excel can be a huge help in many different projects. However, they’re not always necessary – sometimes you want them, sometimes you don’t. In this article, you’re able to learn how to control leading zeros in your spreadsheets, and switch between the different modes depending on your current work.

A leading zero is any “0” digit that comes before the first nonzero digit in a number string. For example the string “001” has two leading zeros. This formatting in Excel can be useful when working with a huge set of data, making organization and sorting much easier.

If you don’t want to type each leading zero yourself manually, there are plenty of methods to add them automatically with Excel.

Adding leading zeros helps to keep numbers neat. It also ensures that all numbers have the same length. This is key for many formulas in Excel. This is one way to master Excel.

Here’s how to remove or add leading zeroes in Excel. These methods will make your data neat, consistent, and ready for any task, from sorting lists to running calculations.

Let’s dive in!

6 Ways to Add Leading Zeros in Excel

There are different ways to add leading zeros in Excel. Let’s explore the possibilities that fit your needs.

Method 1. Custom Number Formatting

Custom number formatting adds zeros in Excel visually without changing the actual data. Here’s how:

1. Select all the cells you want to edit and add leading zeros to. You can do this by dragging your cursor, or holding the Shift key on your keyboard while clicking with your mouse.
2. Right-click on your selection, then choose Format Cells. Alternatively, use the Ctrl + 1 keyboard shortcut to open the window quicker.
3. Select Custom from the Category section or the Number tab.
4. Enter the desired format. Type any number of 0’s in the Type input field. For example: 000000 for six digits (00045).
5. Click OK, and Excel will display the numbers with leading zeros. All of the numbers in your selection should have the corresponding number of leading zeros now.

Please note that this method does not change the value of your cells! The leading zeros are just for appearance. This method keeps the underlying value the same, which is perfect for calculations.

Method 2. Use the TEXT function

If you’re working with text and not number values, you can add leading zeros using the TEXT function. The function changes numbers to text with leading zeros.

To add leading zeros using the TEXT formula, you’ll want to do the following:

1. In any cell, type:=TEXT(cell, “number of zeros“) for example =TEXT(A1, “000000”). Replace “A1” with the cell reference you’re working with.
2. The format “000000” specifies six digits, so Excel will add leading zeros to ensure the number has six digits.
3. Adjust the number of zeros in the format (e.g., “0000” for four digits, “000” for three) based on how many digits you need.

This is going to create a fixed length, in our case 6-character text string. Take note that this method makes it so you’re unable to use your values for calculations and other formulas.

This function is helpful when working with data like postal codes, employee IDs, or other identifiers where leading zeros must be preserved as text.

Method 3. Add Zeros with an Apostrophe

If you’re entering numbers manually, another option is to add the leading zeros in Excel, starting with an apostrophe (‘). This tells Excel to treat the entry as text.

Example:

• Type ‘001 to display the entry as “001” with leading zeros intact.

Excel treats entries with an apostrophe as text rather than numbers, so leading zeros won’t be removed. It’s a great way when you don’t have a lot of data.

This method is quick and ideal for manually entering data, but remember that Excel will store these entries as text, which might affect calculations.

Method 4. The RIGHT Function

The Excel RIGHT function is useful for creating a fixed-length number by adding leading zeros dynamically:

• In a new cell, type =RIGHT(“0000” & A1, 4) to display four digits.
• Replace A1 with your cell reference.

For example, if A1 contains 45, it will display as 0045.

This function works well for ID numbers of varying lengths.

Method 5. Use the REPT and LEN functions

A different approach to adding leading zeros to a text string is by utilizing the REPT and LEN functions together.

Use REPT and LEN to add the exact number of zeros needed based on the length of each entry:

By following this method you won’t be able to continue using functions and calculations.

The formula is as follows:

• Type =REPT(“0”, 6 – LEN(A1)) & A1 to create a four-digit number. [=REPT(0, number of zeros-LEN (cell)) &cell for example =REPT(0, 6-LEN (A1)) &A1]
• Adjust the 4 for more or fewer digits, and replace A1 with your reference cell.

Doing this, Excel will automatically add a number of leading zeros to the value in your A1 cell until it creates a 6 character long string.

This is helpful for columns where the numbers vary widely in length and need specific formatting.

Note: In the abobe formula, if A1 has more than four digits, it won’t add any zeros. Adjust the 4 to set a different target length if needed.

CONCATENATE for Fixed Zeros

For a fixed number of leading zeros, you can use the CONCATENATE function:

• Type =CONCATENATE(“00”, A1) to add two zeros in front of the number in A1.

You can also use the ampersand (&) operator as a shortcut, like this: 00 & A1

How to remove leading zeros in Excel

If you want to revert to your normal numeric values, don’t worry — it’s possible. There are a few methods to use that might fit your approach better.

Method 1. Change the file format

To change the cell format:

1. Select all the cells you want to revert and remove leading zeros from.
2. Switch to the Home tab using the Ribbon interface found on top of your window.
3. In the Number section, you’ll see a drop-down menu displaying Special. Click on this menu to open the other options.
4. Select General. Doing so will revert your cells to the default formatting.

Alternatively:

To change the cell format, first select the cells you want to change. Right-click and choose “Format Cells.” Choose “Number” and set the decimal places to zero. Click “OK.

This action removes leading zeros, allowing Excel to treat your numbers as is. If you entered something like “00123,” it now comes up as 123.

Just like that, you clean up your data for better reading.

Method 2. Convert text to number

If you converted numbers to text to keep those zeros, you can flip them back with ease.

The easiest way to do this is to select the cell you want to convert and then click on the yellow exclamation mark. (Bottom-right corner of the cell.)

Click that one.

From the drop-down menu, simply select Convert to Number. You’ll immediately see it change back to its original numeric value.

Your leading zeros disappear, and you’re left with pure numbers once again.

Sometimes Excel does this automatically, especially with large numbers, converting them to scientific notation.

Method 3. Use Formula to Eliminate Zeros

Want a formula? Try using the TEXT function!

Type =TEXT(A1,”0″) where A1 is your cell reference. This method works like a charm for keeping things tidy while eliminating those pesky leading zeros.

If you need more control, use the REPT function in tandem with TEXT. Say you want two zeros before a number; write =REPT(“0”, 2) & A1. It’s efficient and keeps your data structured.

Additional Tips and Tricks

Hide Leading Zeros in Excel

Highlight the cells containing zeros. Press Ctrl+1 to open the Format Cells dialog. Choose “Custom” and type # for the format code.

This simple step will hide those zeros, making your spreadsheet look cleaner without losing your original data.

Dynamic Zeros with REPT Function

Need to add dynamic leading zeros? The REPT function is your friend!

Suppose you have a number in cell A1, and you want it to show as a five-character string. You can use this formula:

=REPT(“0”, 5-LEN(A1)) & A1

This formula counts how many zeros you need, then adds them before your number. For example, if A1 has “23”, it’ll show “00023”.

Custom Function for Adding Zeros

Write a custom function using the TEXT formula.

If you want to ensure every number has at least five digits, use:

=TEXT(A2, “00000”)

If A2 contains “7,” it becomes “00007.

You can set the number of zeros to however many you want. Just remember, the format codes are the key here.

Conclusion

Adding leading zeros in Excel brings order and clarity to your data. Whether you’re formatting IDs, phone numbers, or codes, using these simple tricks will make your spreadsheets cleaner, more professional, and easier to interpret.

Give these methods a try, and elevate your Excel skills today!