Adding and Removing Leading Zeros in Excel

[nerdslife_team]

Ever tried entering a number like 000652 in Excel and watched those leading zeros vanish? It’s because Excel usually trims them off, as they’re generally not needed for calculations. But, when you’re dealing with IDs, phone numbers, or product codes, those zeros matter! Luckily, there are several ways to handle this, whether you need to add, keep, or remove leading zeros.

How to Add Leading Zeros

You’ve got two main routes to adding leading zeros: formatting numbers as ‘Text’ or using custom formatting. The right choice depends on what you’re going to do with those numbers. If you don’t need the numbers for calculations, then converting them to text is the way to go.

Here are ways to add or pad zeros before numbers by formatting them as text:

• Changing the cell format to text
• Using a leading apostrophe (‘)
• Using the TEXT function
• Using the REPT/LEN functions
• Using the CONCATENATE function or the ampersand (&) operator
• Using the RIGHT function

Changing the Cell Format to Text

This is a straightforward method for keeping leading zeros as you type. By changing a cell’s format to Text, you’re telling Excel to treat your entry as text, preserving it exactly as typed. Here’s how:
Select the cell(s), head to the ‘Home’ tab, find the ‘Format’ dropdown in the ‘Number’ group, and choose ‘Text’.

Now, Excel will keep any leading zeros you enter.

You may spot a small green triangle (an error indicator) at the top-left corner of the cell, signaling that the number is being stored as text.

To get rid of the error, select the cell(s), click the warning sign, and choose ‘Ignore Error’.

Even entering phone numbers with spaces or hyphens will automatically be treated as text by Excel.

Using Custom Number Formatting

The best way to add leading zeros, especially if you want to use the numbers in calculations, is to apply custom number formatting. This way, the displayed number has leading zeros, but the cell value is still a number.

Here are the steps:

1. Select the cells you want to format.
2. Right-click anywhere in the selection and choose ‘Format Cells’ (or just press Ctrl + 1).

3. In the ‘Format Cells’ window, click on the ‘Number’ tab and select ‘Custom’.
4. In the ‘Type:’ box, enter the number of zeros to match the total number of digits you want, for example, 000000 for six digits.

This will display leading zeros and pad numbers to the set length.

Even if a cell is formatted to show leading zeros, the formula bar still shows the original number.

A few digital placeholders you can use in your custom format are:

• 0: displays extra zeros, enforcing a digit whether or not it’s relevant.
• #: displays optional digits, skipping insignificant zeros.
  Punctuation marks and other characters will be displayed as you enter them. You can also create your custom phone number format.

The Format code:

The result:

Let’s apply ##0000 as the formatting code.

Here’s how different placeholders work:

You can also use predefined format codes in the ‘Special formats’ section for things like postal codes, phone numbers, and social security numbers.

The following table shows how different “Special” format codes can change the display of numbers with leading zeros:

Using the TEXT Function

If you need to add leading zeros to an existing list of numbers, the TEXT function is what you need. It lets you convert a number to a text string while applying a specific format. The syntax is:

= TEXT(value, format_text)

• value is the number you want to format.
• format_text is the format you want to apply (e.g., “000000” for six digits).

For example, to make numbers six digits long with leading zeros, the formula looks like:

=TEXT(A2,"000000")

Drag the fill handle to apply to the rest of the cells.

Note that TEXT function always returns a text string, so you can’t use them in mathematical calculations but you can use them in lookup formulas.

Using Leading Apostrophe ( ‘ )

One simple way to add leading zeros is to type an apostrophe (‘) before the number. This forces Excel to treat the entry as text.

The apostrophe won’t be visible in the cell unless you select it.

Using CONCATENATE Function or Ampersand Operator (&)

To add a specific number of leading zeros to all numbers in a column, you can use the CONCATENATE function or the & operator.

The syntax for CONCATENATE is:

=CONCATENATE(text1, [text2], ...)

And for the & operator:

=Value_1 & Value_2

For example, to add two leading zeros:

=CONCATENATE("00",A2)

or

="00"&A2

These formulas add two leading zeros to each number and store them as text.

Using REPT/LEN functions

The REPT function repeats characters a specific number of times, which can be useful to add leading zeros. The syntax is:

=REPT(text, number_times)

To add five zeros:

=REPT(0,5)&A2

To create fixed-length strings, you can use REPT and LEN together:

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

This will add enough leading zeros to make a 5-character string.

Using RIGHT function

Another way is to use the RIGHT function. This function extracts the rightmost N characters from the value:

= RIGHT (text, num_chars)

To create a 6-digit number, the formula can be:

=RIGHT("0000000"&A2,6)

The first argument (text) adds 7 zeros to the number in A2, then RIGHT extracts the rightmost 6 characters.

---

How to Remove Leading Zeros

Sometimes, data comes with unwanted leading zeros, usually formatted as text. Here’s how to remove them and convert the values back to numbers.

Remove Leading Zeros by Changing the Cell Formatting

If leading zeros were added through custom formatting, removing them is simple. Just change the format of the cells back to ‘General’ or ‘Number’. You can confirm a cell has custom formatting if you see leading zeros in the cell but not in the formula bar.

To remove them, select the cells, click the ‘Number Format’ box, and choose ‘General’ or ‘Number’.

The leading zeros will vanish.

Delete Leading Zeros by Converting Text to Numbers

If leading zeros were added by formatting the cell as text, adding apostrophes or during importing, the easiest way to remove them is with the error checking option.
You can do this when you see a green triangle at the top left corner of the cells.

Select the cells, click on the yellow warning sign, and select ‘Convert to Number’ from the drop-down menu.

The leading zeros will be removed and the numbers will be formatted correctly.

Remove Leading Zeros by Multiply/Dividing by 1

Multiplying or dividing by 1 doesn’t change the value of a number, but it does convert it from text to number, effectively removing leading zeros.

Just type the formula in a cell and press ENTER. The leading zeros will be removed.

Apply this to other cells with the fill handle.

You can achieve the same using ‘Paste Special’. Type 1 in a cell, and copy it.

Select the cells you need to change, right-click, and select ‘Paste Special’.

Choose either ‘Multiply’ or ‘Divide’ and click ‘OK’.

The leading zeros will be gone.

Remove Leading Zeros by using a Formula

You can also remove leading zeros using the VALUE function.

=VALUE(A1)

This converts the text value in A1 to a number and removes the leading zeros.

If you want to keep numbers as text (but without the zeros), use:

=TEXT(VALUE(A1),"#")

Remove Leading Zeros using Excel’s Text to Columns Feature

The Text to Columns feature is another way to eliminate leading zeros.

Select the cells with numbers that have leading zeros.

Go to the ‘Data’ tab and select ‘Text to Columns’.

In Step 1, choose ‘Delimited’ and click ‘Next’.

In Step 2, uncheck all delimiters and click ‘Next’.

In the final step, leave the format as ‘General’ and specify the destination cell. Then click ‘Finish’.

The numbers, without leading zeros, will appear in a separate column.