How to Remove Leading Zeros in Excel (7 Super Easy Ways)

The data you’re dealing with may have values with leading zeros because the data was created or collated by someone else according to their selected layout or maybe it was your own selected layout and now it's time to change it.

By default, Excel will ignore the leading zeros when typed as a number and enter the number without the leading zeros. This implies that having the leading zeros was either due to a leading apostrophe, a custom number format, or as a result of a formula.

Well now the zeros are here and we need to deal with them. Removing a fixed number of leading zeros is not a problem at all but removing a varied amount of leading zeros is….. also not a problem! You will find out it isn’t with this tutorial today on how to remove leading zeros in Excel.

Our techniques to remove leading zeros from numeric values involve using the error checking option, the VALUE function, Text to Columns, and VBA, multiplying the column by 1 or adding 0 to it, and using a formula especially to deal with non-numeric values.

Let’s get removing!

Method #1 – Converting Text to Numbers

If you see a number with leading zeros in Excel, there’s a chance it has been entered as a text string because with numbers in general, Excel will ignore the typed leading zeros and display the number forth of the leading zeros. Therefore, leading zeros will only remain as part of a numeric value with custom number formats and text formats. A small example is this:

Here we have a group of numbers with leading zeros that have been entered with a leading apostrophe. The apostrophe converts the number to text (some punctuation marks will convert numbers to text) as we can see that the numbers are left-aligned instead of right-aligned as numbers are by default. Another very obvious indicator is the Number Stored as Text error displayed on each cell.

The theory is to convert the format of the cells to a number format so it ignores the leading zeros and displays the numbers without them. Let’s see how that works out.

• Select the cells with the errors.

• Click on the exclamation point error icon and choose the Convert to Number option from the list.

This will convert the selected cells to numbers that inherently will ignore the leading zeros and hence, remove them:

Another option would be to remove the apostrophe which will also have the same effect; it will leave the leading zeros unpunctuated which will then be ignored.

Method #2 – Using VALUE Function

The VALUE function converts a numeric text string to a number. Isn’t this function made particularly for this guide? That makes the function easy to use and understand since it’s built for the very purpose of converting text to numbers. As explained in the previous section, converting numeric text to number will remove the leading zeros. Let’s see the formula:

The VALUE function only needs a single argument for converting a numeric text to a number. The function has been supplied with the cell reference of the numeric text. VALUE converts the text to a number and the number ignores the leading zeros, effectively removing them and returning the residual numbers.

The example shows that this method of removing leading zeros using the VALUE function applies to numbers stored as text with an apostrophe and also to custom number formats.

Method #3 – Multiplying the Column with 1 or Adding 0

Multiplying the leading zeros values with 1 or adding 0 to them will remove the leading zeros. The mechanics of this is to subject the number with leading zeros to a calculation that will not change its original value. The answer to the calculation will result in a number without the leading zeros. Clever, right?

Do note however that a calculation is only possible with numbers. This method will not work for mixed text strings e.g. 000A010 but works equally well for custom number formats and numbers stored as text.

There are a few ways to multiply the column with 1 which we have shortly described at the end of this section. Right below is our method of using a formula. Here is the formula itself:

The formula is a simple multiplication of cell B3 by the number 1. The calculation basically becomes:

0000501 x 1

Now obviously Excel isn't going to return 0000501 as the answer. The fundamental calculation is 501 multiplied by 1 and the answer returned is 501, leading zeros bye-bye.

The same principles apply with adding zeros to the numbers with leading zeros. Both calculations, multiplying by 1 and adding 0 maintain the original value of the number.

Other ways of multiplying the column by 1 (also applies to adding 0) include adding a column with 1 or multiplying the column with a cell with 1 as its value or using Paste Special.

To multiply the column with 1, add a column with 1s (e.g. enter 1 in C3 and copy it down to C14) and multiply the two values; the number with the leading zeros and 1. The formula to apply will become B3*C3.

To multiply the column with a cell, enter 1 in any empty cell e.g. E2. Then multiply the column with E2. The formula will be B3*$E$2.

The way with Paste Special is explained in the next section.

Let’s agree that B3*1 is the quickest and neatest way of the first two methods; no new column, no new cell required.

Method #4 – Adding 0 to the Column Using Paste Special

Using the Paste Special feature, we can add 0 to the column to remove leading zeros. The zero can be added….. without even typing the zero! The secret is to copy an empty cell and add that to the column by special-pasting.

Essentially, an empty cell has no value, and therefore its value is 0. With Paste Special, the value can be added to the column. Adding zero to the column draws the numbers with leading zeros into the calculation. The calculation requires numbers and so the column is converted to numbers and we know by now that converted to numbers, the leading zeros will be gone.

Here we have the steps for using Paste Special to add zero to the column to remove leading zeros:

• Copy any empty cell on the worksheet.

• Select the cells in the column with the leading zeros.

• Right-click the selection and choose Paste Special from the menu to open the Paste Special dialog box. Or press the Ctrl + Alt + V keys.

• In the Paste Special dialog box, select the Add radio button. Then click on the OK command button.

The value copied from the empty cell i.e. 0 will be added to the selected cells, eliminating the leading zeros:

Again, in the same way we can use the Multiply operation in Paste Special to copy 1 from a cell and special-paste it on the numbers with the leading zeros to multiply the column by 1.

Another equally quick method for removing leading zeros would be to add the column with leading zeros to an empty column. Considering column E to be an empty column, the formula to add the two columns would be:

Method #5 – Using Text to Columns

With the Text to Columns feature, a single column of text can be split into multiple columns according to any specified conditions. For removing leading zeros, we will make use of the General data format which, among other conversions, converts numeric values to numbers. If the values in our example are turned into numbers, the leading zeros will be removed. These are the steps to use Excel Text to Columns to remove leading zeros:

• Select the range with the numbers with leading zeros.
• Go to the Data tab and select the Text to Columns

 In the Text to Columns wizard, select the Delimited radio button and then hit the Next

•  Uncheck all the Delimiters. Then select the Next button.

•  Leave the Column Data Format as General. Set the first destination cell for the column of the new numbers in the Destination field. We have set C3 as the destination cell.
• If you want the original values to be overwritten, set the first cell of your data as the destination cell. For our example, that would be B3.
• Click on the Finish button when done.

 The new values without the leading zeros will be entered beginning from the set destination cell:

Method #6 – Using VBA

Another way of applying the General Number format to the numeric values is to apply it using VBA. But that's not the only thing we're going to do today. We will assign the VBA Macro to a user-created button on the active worksheet. Let’s begin with adding the Macro code to our worksheet with VBA to remove leading zeros:

• In the Developer tab, select the Visual Basic icon from the Code group to open the Visual Basic. Or press the Alt + F11 keys.

• The Visual Basic editor will have opened:

• Click on the Insert tab and select Module to launch a Module

• In the Module window copy-paste the Macro code below:

Sub Remove_Leading_Zeros()
Dim Delete_Range As Range
Dim Work_Range As Range
On Error Resume Next
xTitleId = "Remove Leading Zeros"
Set Work_Range = Application.Selection
Set Work_Range = Application.InputBox("Range", xTitleId, Work_Range.Address, Type:=8)
Work_Range.NumberFormat = "General"
Work_Range.Value = Work_Range.Value
End Sub

This code will be used to convert the numeric values to the General number format. The General format will cause the leading zeros to be ignored and removed.

The code can be run from the module window immediately by pressing the F5 key. The code can also be run from the worksheet tabs but let’s add a button for the macro so that running the code is only a click away.

• Close the Visual Basic

• Go to the Developer tab > Controls group > Insert button > Button (the first Form Control).

• Now the Assign Macro dialog box will pop up so a macro can be assigned to the button.
• Select the relevant Macro to remove the leading zeros and then select the OK

• The cursor will change to a cross sign pointer for the button to be added in the preferred size.
• Click and drag the cross pointer to form the button in the area and size of choice.

• Rename the button (optional).

• To run the code from the button, select the cells with the numeric values and then click on the added Remove Leading Zeros
• Confirm the selection in the popped-up dialog box and then click on OK.

Using the button will run the Macro code added using the Visual Basic editor. The General format will be applied, removing the leading zeros from the selected range:

Method #7 – Remove Leading Zeros from Non-Numeric Values

All the methods in this guide to remove leading zeros have centered on converting the numeric values to numbers. If the data consists of mixed values e.g. product codes such as 000A010, it won’t be possible to convert them to numbers to remove the leading zeros. For removing leading zeros from non-numeric values, we’d have to employ a formula and the one we’re employing is:

Here’s what this formula is doing. The SUBSTITUTE function replaces the 0s in B3 with empty text (as denoted by the double-quotes ""). The result of SUBSTITUTE is A1. From this text string, the LEFT function returns 1 character from the left, which in this case is A. Then the FIND function returns the starting position of A within B3. A is the 4th character in B3 which makes the result of the FIND function 4.

The LEN function returns the number of characters in B3 i.e 7 and the formula then subtracts 4 from 7; we are left with 3. Lastly, the RIGHT function is to return 3+1 characters from the right of the value in B3. It finally returns A010.

That was all we had to say on how to remove leading zeros in Excel. We went through getting rid of leading zeros from numeric values and mixed values and we’re certain you’ll be able to find a solution with leading ease. While you’re shooting those zeros down, we’ll be back with more troubleshooters and how-tos to handle things the Excel way.