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!

Table of Contents

## 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.

## 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:

`=VALUE(B3)`

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.

## 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:

`=B3*1`

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.

## 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:

`=B3+E3`

## 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*

*Delimiters.*Then select the*Next*button.

*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:

## 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:

## 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:

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

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.