How to Remove Leading Zeros in Excel (6 Easy Ways)

Many people have a love-hate relationship with leading zeros in Excel.

Sometimes you want it, and sometimes you don’t.

While Excel has been programmed in such a way that it automatically removes any leading zeros from the numbers, there are some cases when you may have these.

In this Excel tutorial, I will show you how to remove the leading zeros in your numbers in Excel. I’ll also show you a formula for text values like part numbers, where the zeros sit in front of letters.

The right method depends on where the zeros came from. If they were typed with an apostrophe, the Convert to Number option fixes them in a couple of clicks, and if a number format adds them, switching to General does it.

So let’s get started!

Possible Reasons You May Have Leading Zeros in Excel

As I mentioned, Excel automatically removes any leading zeros from numbers. For example, if you enter 00100 in a cell in Excel, it would automatically convert it into 100.

In most cases, this makes sense as these leading zeros are not really meaningful.

But in some cases, you may want it.

Here are some possible reasons that may cause your numbers to retain the leading zeros:

• If the number has been formatted as text (mostly by adding an apostrophe before the number), it would retain the leading zeros. The same happens when the cell was formatted as Text before you typed the number.
• The cell may have been formatted in such a way that it always shows a certain length of a number. And in case the number is smaller, leading zeros are added to make up for it. For example, you can format a cell to always show 5 digits (and if the number is less than five digits, leading zeros are added automatically)
• In Excel for Microsoft 365 and Excel 2024, the ‘Remove leading zeros and convert to number’ option may have been turned off (File > Options > Data > Automatic Data Conversion, or Excel > Preferences > Edit on a Mac). With it off, anything you type with leading zeros is stored as text.

This often comes up when a lookup fails. One list has 001375 stored as text, the other has 1375 as a number, and Excel doesn’t see them as a match.

The method we choose to remove the leading zeros would depend on what is causing it.

Also read: How to Add Leading Zeroes in Excel

So, the first step is to identify the cause so that we can choose the right method to remove these leading zeros.

A quick way to tell is to look at the alignment (unless someone has changed it). Text sits on the left of the cell, often with a small green triangle in the corner, while a real number sits on the right.

So if the value is on the left, it’s stored as text. If it’s on the right but still shows zeros, a number format is adding them, and the sections below cover both cases.

Before you remove anything, make sure the zeros aren’t part of the data itself. Some codes, like ZIP codes and phone numbers, need their zeros, so it’s best to keep those as text.

There are multiple ways you can use to remove the leading zeros from numbers. In the sections below, I will show you five such methods, and then a formula for text values.

Remove Leading Zeros Using the Error Checking Option

If the cause of leading zeros is that someone has added apostrophe before these numbers (to convert these into text), you can use the error checking method to convert these back into numbers with a single click.

This is probably the easiest method to get rid of leading zeros.

Below I have a list of customers, where the customer IDs in column B were entered with an apostrophe, and I want to remove the leading zeros from these IDs.

This is also the reason you see these numbers aligned to the left (whereas by default numbers align to the right) and also have the leading 0’s.

Below are the steps to remove these leading zeros from these numbers:

1. Select the numbers from which you want to remove the leading zeros (B2:B11 in this example)

2. Hover over the small green triangle in the top-left corner of cell B2. This opens the Number Stored as Text card

3. Click on ‘Convert to Number’

In some older versions of Excel, you’ll see a yellow error checking icon next to the selection instead of the card. Click on it, and then click on ‘Convert to Number’ in the menu that opens.

That’s it! The above steps would remove the apostrophe and convert these text values back into numbers.

And since Excel is programmed to remove leading zeros from any numbers by default, you’ll see that doing this automatically removes all the leading zeros.

Note: This method would not work if the leading zeros have been added as a part of the custom number formatting of the cells. To handle those cases, use the methods covered next.

Remove Leading Zeros by Changing the Number Format to General

Another really common reason that may make your numbers show up with leading zeros is when your cells have been formatted to always show a specific number of digits in each number.

A lot of people want to make the numbers look consistent and of the same length, so they specify the minimum length of the numbers by changing the formatting of the cells.

For example, if you want all the numbers to show up as 5 digits numbers, if you have a number which is only three-digit Excel would automatically add two leading zeros to it.

Below I have a list of customers where custom number formatting has been applied to the IDs in column B to always show a minimum of six digits, and I want to remove the leading zeros from these IDs.

You can confirm that it’s the formatting by selecting any of these cells. The formula bar shows the actual number (1375 for Rachel Kennedy) with no zeros in front of it.

And the way to get rid of these leading zeros is just to remove the existing formatting from the cells.

Below are the steps to do this:

1. Select the cells that have the numbers with the leading zeros

2. Click the Home tab

3. In the Number group, click on the Number Format dropdown

4. Select ‘General’

The above steps would change the custom number formatting of the cells and now the numbers would be displayed as expected (where there would be no leading zeros).

Note that this technique would only work when the reason for the leading zeroes was custom number formatting. It would not work if an apostrophe has been used to convert numbers to text (in which case you should use the previous method)

Remove Leading Zeros with Paste Special (Add a Blank Cell)

This technique works in both scenarios (where the numbers have been converted into text by using an apostrophe or a custom number formatting has been applied to the cells).

Suppose you have a data set as shown below, where some customer IDs in column B are text and some are numbers with a custom format, and you want to remove the leading zeros from all of them.

Below are the steps to do this:

1. Copy any empty cell from the worksheet

2. Select the cells where you have the numbers from which you want to remove the leading zeros

3. Right-click on the selection and then click on Paste Special. This will open the Paste Special dialog box

4. Click on the ‘Add’ option (in the Operation group), and leave ‘All’ selected in the Paste group

5. Click OK

The above steps add 0 to the range of cells you selected and also remove all the leading zeros and apostrophe.

While this does not change the value of the cell, it converts all the text values into numbers. It also copies the formatting from the blank cell that you copied (thereby replacing the existing formatting that was making the leading zeros show up).

That’s why ‘All’ needs to stay selected. If you pick ‘Values’ instead, the text IDs still lose their zeros, but the formatted numbers keep showing them because their format stays in place.

Since the blank cell’s formatting comes along, any borders on the selected cells go away as well, so you may need to add them back.

This method only impacts the numbers. In case you have any text string in a cell (such as Pending in cell B9), it would remain unchanged.

Formula to Remove Leading Zeros (VALUE Function)

Another quick and easy method to remove the leading zeros is to use the VALUE function.

This function takes one argument (which could be the text or the cell reference that has the text) and returns the numerical value.

This would also work in both scenarios where your leading zeros are a result of the apostrophe (used to convert numbers to text) or the custom number formatting.

Suppose I have a data set as shown below, with a mix of text and formatted customer IDs in column B, and I want the IDs without the leading zeros in column C.

Below is the formula that would remove the leading zeros:

=VALUE(B2:B11)

I entered this formula in cell C2, and it spills down to C11 on its own, so there’s no need to copy it down. The results are real numbers, which is why they line up on the right side of the cells.

If you are using Excel 2019 or earlier, enter the below formula in cell C2 and then copy it down to C11:

=VALUE(B2)

You may also come across the double minus trick (=--B3) for this. It converts the text the same way, but on an ID with the six-digit custom format (like B3), its result picks up that format, so it still shows 001375.

The VALUE function doesn’t carry that format over, which makes it the better pick here.

If the cells where you enter the formula already have a custom number format, the results can still show leading zeros.

Note: In case you still see the leading zeros, you need to go to the Home tab and change the cell format to General (from the Number Format dropdown).

Remove Leading Zeros Using Text to Columns

While the Text to Columns functionality is used to split a cell into multiple columns, you can also use it to remove the leading zeros.

Suppose you have a data set as shown below, where the customer IDs in column B were entered as text, and you want the IDs without the leading zeros in column C.

Below are the steps to remove leading zeros using Text to Columns:

1. Select the range of cells that have the numbers (B2:B11 in this example)

2. Click on the ‘Data’ tab

3. In the Data Tools group, click on ‘Text to Columns’. This opens the Convert Text to Columns wizard

4. In Step 1 of 3, select ‘Delimited’ and click on Next

5. In Step 2 of 3, deselect all the delimiters and click on Next

6. In Step 3 of 3, keep the column data format as General, select a destination cell (C2 in this case), and click on Finish

The above steps should remove all the leading zeros and give you only the numbers in column C. In case you still see the leading zeros, you need to change the formatting of the cells to General (this can be done from the Home tab)

Pro Tip: If you don’t need to keep the original IDs, you can click Finish right in Step 1 of the wizard. Excel then converts the IDs in place and the leading zeros are gone.

Remove Leading Zeros from Text (Formula)

While all the above methods work great, these are meant for only those cells that have a numerical value.

But what if you have alphanumeric or text values that also happened to have some leading zeros in it.

The above methods would not work in that case, but thanks to amazing formulas in Excel, you can still get this done.

Suppose you have a list of part numbers in column A as shown below, and you want to remove all the leading zeros from them in column B.

If you’re using Microsoft 365, the simplest way to do this is with the REGEXREPLACE function. Below is the formula to do that:

=REGEXREPLACE(A2:A11,"^0+","")

How this formula works:

• The pattern “^0+” looks for zeros at the start of the text. The ^ means the start of the text, and 0+ means one or more zeros in a row.
• The third argument (“”) replaces those zeros with nothing, so only the leading zeros are removed.
• Zeros later in the text are left alone, which is why 0600AX1 becomes 600AX1.
• Since the formula refers to the whole range, it spills down to B11 on its own.

The REGEXREPLACE function is only available in Excel for Microsoft 365. It’s not in Excel 2024, 2021, or earlier versions.

If a cell has only zeros (like 0000), this formula leaves it blank. In case you want to keep a single 0 there, use “^0+(?=.)” as the pattern instead.

The example file has the REGEXREPLACE result in column B and an older-version formula in column C, so you can compare the two.

If you are using Excel 2024 or an earlier version, enter the below formula in cell C2 and then copy it down to C11:

=RIGHT(A2,LEN(A2)-FIND(LEFT(SUBSTITUTE(A2,"0",""),1),A2)+1)

Since REGEXREPLACE isn’t available in older versions, column B in the example file shows a #NAME? error there, while column C works in every version.

Let me explain how this formula works

The SUBSTITUTE part of the formula replaces the zero with a blank. So for the value 0041K27, the substitute formula gives the result as 41K27

But SUBSTITUTE removes every zero, not just the leading ones (0600AX1 would become 6AX1), so its result can’t be used as the answer. The formula only uses it to find the first non-zero character.

The LEFT formula then extracts the left-most character of this resulting string (41K27), which would be 4 in this case.

The FIND formula then looks for this left-most character given by the LEFT formula and returns its position. In our example, for the value 0041K27, it would give 3 (which is the position of 4 in the original text string).

The result of the FIND formula is then subtracted from the result of the LEN formula (which is used to give the length of the entire text string). For 0041K27, that’s 7 minus 3.

1 is added to this result, to make sure that we get the entire text string extracted (except the leading zeros). So we get 5, which is the length of the text string without the leading zeros.

This value is then used with the RIGHT function to extract the entire text string (except the leading zeros).

One thing to keep in mind is that if a cell has only zeros (like 0000), this formula returns it unchanged. That’s because there’s no non-zero character for the FIND formula to look for.

In case there is a possibility that there may be leading or trailing spaces in your cells, it’s best to use the TRIM function with every cell reference.

So the new formula with the TRIM function added would be as shown below:

=RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND(LEFT(SUBSTITUTE(TRIM(A2),"0",""),1),TRIM(A2))+1)

So, these are some easy ways that you can use to remove leading zeros from your data set in Excel.

I hope you found this tutorial useful!

Other Excel tutorials you may like:

• Hide Zero Values in Excel | Make Cells Blank If the Value is 0
• How to Replace Blank Cells with Zeros in Excel Pivot Tables
• Delete Rows Based on a Cell Value (or Condition) in Excel
• How to Stop Excel from Changing Numbers to Dates
• How to Convert Serial Numbers to Dates in Excel
• Remove Characters From Left in Excel (Easy Formulas)
• 10 Super Neat Ways to Clean Data in Excel Spreadsheets