How to Remove Leading Zeros in Excel?

Excel, by default, automatically removes leading zeroes whenever you enter numbers. So, you might be wondering why there are leading zeroes in the Excel file you received.

Well, there are two possible reasons for that. It could be because:

  1. The cell is in text format.

This can happen if there’s an apostrophe at the start of the cell. Apostrophe (‘) is a special character that tells Excel to treat a cell as a text. If you are working on an Excel file exported from a system, there’s a great chance you’ll encounter this.

Remember that adding an apostrophe is just one way of converting a cell into a text. A cell can also be converted into text by simply changing its number format – so check on that too.

  • The cell is formatted to have a fixed number of digits.

If you have a 3-digit number entered in a cell that was formatted to always have 5 digits, Excel will automatically fill in the missing 2 digits with leading zeroes.

There are plenty of ways to remove the leading zeroes in Excel. They vary depending on the reason for having them. I’ve arranged the methods below to tackle first the most common causes. I suggest you go through each of them and see what works on your data.

Error Check to Remove Leading Zeroes in Excel

Excel will typically warn you if you have cells that contain numeric values but a formatted as text. You will notice this when you see small green triangles added on the left side of these cells (as shown below).

Small green triangles on the left side of cells appear whenever the cells contain numeric values but are formatted as text

If you don’t see them, it’s most likely that Background Error Checking is disabled on your Excel.

To enable it, simply go to File >> More >> Options.

Go to File >> More >> Options.

In the Excel Options menu, select Formulas.

Go to the Error Checking section, tick the “Enable background error checking” checkbox, and click OK.

Steps to enable background error checking in Excel

You should now see the small green triangles on the left side of your cells.

(If you don’t see them still, please proceed to the method that follows after this).

Select all these cells, and a yellow warning icon will crop up on the first cell selected.

A yellow warning icon crops up after selecting cells with green triangles.

Click on that icon and select Convert to Number.

And that’s it! All leading zeroes should now disappear.

Sample output after letting Excel convert cells to number format

With this approach, we have taken advantage of Excel’s default setting of removing leading zeroes from numbers.

Changing the Number Format to Remove Leading Zeroes in Excel

If the previous method didn’t work for you, it might be because your cells are in a “Special” format where leading zeroes are automatically added so that it reaches a particular number of digits.

To change this:

1. Select all the cells with leading zeroes.

Select all cells with leading zeroes

2. Go to the Home tab. Look at the current number formatting of these cells from the Number Section. If it is “Special”, change the number formatting to General (or any other number formatting appropriate for your data).

Steps to change selected cells' number formatting

And that’s it! You should no longer see any leading zeroes in your selected cells.

Sample output after changing cells' number formatting to General

Using “Paste Special” to Remove Leading Zeroes in Excel

If you don’t want to alter the cells containing leading zeroes and would only want the numbers on a different column, then you can do the following:

1. Select a blank cell on the worksheet and press CTRL + C to copy it.

Select a blank cell and copy it

2. Next, select all the cells with leading zeroes.

Select all cells with leading zeroes.

3. Then, press CTRL + ALT + V to open the Paste Special menu.

Select the Add Operation from the Paste Special menu and click OK.

Select Add under the Operation section and click OK.

And that’s it! You should now have removed all the leading zeroes from your cells.

Sample output after using the Paste Special option to remove leading zeroes.

This method worked because it added a zero (or a blank cell) to all the cells with leading zeroes, which in the process, converted the cells into numbers.

Using the VALUE Function to Remove Leading Zeroes in Excel

Another way to remove leading zeroes without altering the original column is to use the VALUE() function.

VALUE() is a text function in Excel that converts a text string that looks like a number into an actual number.

On a new column, type the following formula: =VALUE([range])

Enter the VALUE() formula

As you press enter, the leading zeroes will disappear.

Copy this formula to the remaining rows, and voila! No more leading zeroes for the entire column.

Using Text to Columns to Remove Leading Zeroes in Excel

If you have a lot of rows to remove the leading zeroes from, this may be the best option for you.

1. Highlight the entire column that contains leading zeroes.

Select the entire column with leading zeroes.

2. Go to the Data tab. From the Data Tools section, click Text to Columns.

Steps to open Text to Columns wizard.

3. The Convert Text to Columns Wizard will appear.

Select Delimited and click Next.

In the Text to Column Wizard, select Delimited.

4. Uncheck all the Delimiters checkboxes and click Next.

In the Text to Columns wizard, uncheck all "Delimiters" checkboxes.

5. In the Column Data Format, select General.

Notice there’s a note in the Wizard that says:

'General' converts numeric values to numbers, date values to dates, and all remaining values to text.

This is the Text to Column feature that we are making use of.

In the Text to Column wizard, select the General format.

Click Finish to close the Wizard.

You should now see all the leading zeroes removed from your selected column.

Sample output after running the Text to Columns wizard

If you have used Text to Columns before and worry that this may affect other columns alongside the current field, don’t worry. Only the selected column gets updated with the settings we selected in the Wizard.

Remove Leading Zeroes from an Alphanumeric Data

The methods described above involved the conversion of the cell into a number format.

If you are working on data with alphanumeric characters (a combination of both letters and numbers) and you only want to remove the leading zeroes, then this next method is for you.

1. On a blank column, type this formula:


Change all instances of “A2” with the range where your alphanumeric data is.

Add formula to remove leading zeroes from a cell with alphanumeric characters.

2. Once you click enter, the cell should result in an alphanumeric character with no leading zeroes.

3. Copy this formula to the remaining rows in your cells, and you’re all set up.


Leading zeroes are often intentionally added for a particular purpose. However, there are instances when we need to remove them. I hope the methods provided above have helped you do so with ease. 

Leave a Comment