How to keep or remove leading zeros

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

 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.

keep remove leading zeros1

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

keep remove leading zeros2

4# let’s see the result.

keep remove leading zeros3

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.

keep remove leading zeros4

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.

keep remove leading zeros5

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.

keep remove leading zeros6

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

keep remove leading zeros7

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.

Get the position of the nth using excel vba1

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

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

convert column number to letter3

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

Sub RemoveLeadingZero()
    Dim WorkRng As Range
    On Error Resume Next
    xTitleId = "Remove leading zeros"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    WorkRng.NumberFormat = "General"
    WorkRng.Value = WorkRng.Value
End Sub

keep remove leading zeros8

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

keep remove leading zeros9

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

keep remove leading zeros10

 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)…

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


So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *