In the previous post, we talked that how to add leading zeros in the front of a number of text string. And this post explains that how to keep leading zeros as you type in excel. How to prevent automatic truncation of leading zeros in excel. How to remove or delete the leading zeros.

Assuming that you have a list of zip codes in your worksheet, and when you type the zip codes that contains leading zeros, the excel will truncate the leading zeros automatically by default. Such as, you try to type a zip code like “00010086” in a cell, and it will change to “10086”.

1. Keep the leading zeros

If you want to keep the leading zeros as you type in a cell, you need to change the cell format to Text before you type the zip code number. Let’s refer to the following steps:

1# select the range of cells that you want to type the zip codes that contain leading zeros.

2# right click on the selected range, select “Format Cells” from the pop-up menu. The Format Cells window will appear.

3# switch to the Number tab, then choose Text from the Category list.  Click OK button.

4# let’s see the result.

Note: you will see a small green triangle in the top-left corner of the cells with leading zeros, it indicates there are some errors with the cell contents. If you want to remove it, you need to select the cells, then click the warning sign, next to click “Ignore Error” from the pop-up menu.

There is another way to keep leading zeros as you type, you just need to insert an apostrophe in front of the number you type.  And the apostrophe character will not be displayed in the cell, but excel will treat the contents of the cell as text format.

2. Remove the Leading zeros

If you have a list of phone number with leading zeros, and all numbers are treated as Text format by Excel. if you want to remove all leading zeros in the front of the phone number in a cell, you just need to select the cell,  click exclamation point, then select “Convert to Number” from the pop-up menu.

Or you can use the VALUE function to convert the text to number.

3. Remove the Leading zeros using VBA macro

You can remove or delete leading zeros in front of the cells with excel VBA macro, just refer to the below steps:

1# click on “Visual Basic” command under DEVELOPER Tab.

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

4# paste the below VBA code into the code window. Then clicking “Save” button.

Sub RemoveLeadingZero()
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select one Range that you want to remove leading zero:", "RemoveLeadingZero", WorkRng.Address, Type:=8)
    WorkRng.NumberFormat = "General"
    WorkRng.Value = WorkRng.Value
End Sub

5# back to the current worksheet, then run the above excel macro, select the range that you want to delete leading zero.

6# click “OK” button, you will see that all leading zeros have been removed in the selected range.

4. Video: Remove the Leading zeros

This Excel video tutorial, we’ll explore three methods to manage leading zeros in your data. We’ll start by learning how to keep leading zeros using the Format Cells feature. Then, we’ll discover how to remove leading zeros using Excel’s built-in functions. Finally, we’ll dive into a more advanced approach by converting to numbers using VBA code.

https://youtu.be/xu4Q8gKcCS4

5. Related Functions

• Excel VALUE function
  The Excel VALUE function converts a text value that represents a number to a numeric value.The syntax of the VALUE function is as below:= VALUE  (text)…
• Excel Text function
  The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…

 6. Related Posts

• How to insert leading zeros to number or text
  Assuming that you want to insert leading zeros  to number in Cell A2, then you can try to use the TEXT function to create an excel formula. You can also use the CONCATENATE function to add the specific digit of leading zeros into each number…
• Count Numbers with Leading Zeros
  Suppost you have a list of product ID in your worksheet, and the product ID contains the different number of leading zeros in the front of string. how to preserve the leading zeros while counting numbers. And you can use another Excel function named as SUMPRODUCT.…