Your data may contain leading zeros and it does not matter how this was gathered, you can change it by removing these zeros. Excel, by default, does lots of functions and in this case, these leading zeros will be ignored when you add a number without the leading zeros. It simply shows that the presence of leading zeros is just because there is a leading apostrophe, a custom number format, or because of a formula.
For now, your data has leading zeros and you need to remove them. Well, it is not a big issue to remove a fixed number however; removing a varied amount of zero could be problematic. Today we are going to discuss how to remove leading zeros in Excel to make the procedure simple you can go through this post till the end.
Below are some useful methods you can follow:
- Using a Custom Number Format
- Using Non-Numeric Values
- Using VBA
Let’s get started with our first method:
How to Remove Leading Zeros in Excel Using a Custom Number Format?
Do you know how to tell if the cells are formatted using a custom number format or not?
The leading zeros will appear when you click on the cell and they will not display on the formula bar. How the cell look determines the custom number format. You cannot change the cell type. Furthermore, by clicking on the Number group given under the Home tab, you will find the format as Custom or Special.
Now, leading zeros can be removed with just a single click. Click on the drop-down menu and you can change the format back into the General.
How to Remove Leading Zeros Using Non-Numeric Values?
The above method uses the rule to convert the numeric values to numbers. And most of the methods specified for this query using this approach of converting values. However, if your data contains mixed values such as product codes (000A010), it would be difficult to change the values to numbers.
That’s why converting values to numbers is not the possible solution always. You can implement the following formula to remove leading zeros from non-numeric values.
=RIGHT(B3, LEN(B3) – FIND(LEFT(SUBSTITUTE(B3, “0” , “”),1), B3)+1)
This formula is capable to do multiple things.
The SUBSTITUTE function in the formula can convert the 0s in B3 with blank text, which is denoted as the double quotes. A1 is the result of SUBSTITUTE. From this text string, you will notice the LEFT function returns 1 character from the left and it would be A.
The FIND function on the other hand returns the starting position of A in B3. A is the 4th character in B3 that makes the result of the FIND function 4.
Moreover, the LEN function can return the number of characters in B3 such as 7 and the formula lets you minus 4 from 7 and only 3 will be left there.
The RIGHT function is helpful to return 3+1 characters from the right side of the value in B3 and it eventually returns A010.
How to Remove Leading Zeros in Excel Using VBA?
One of the all-rounder methods used to remove leading zeros is applying the VBA format. You will be able to hand over the VBA Macro to a user-created button on the active worksheet. So, let’s get started with the Macro code to put in the worksheet with VBA:
From the Developer tab, choose the Visual Basic button given in the Code group. It will open the Visual Basic option. Or else you can press the ALT + F11 keys for this step.
Now, you will notice the Visual Basic editor is opened:
Choose the Insert tab and select Module.
In this window, copy and paste the Macro code given below:
Sub Remove_Leading_Zeros()Dim Delete_Range As RangeDim Work_Range As RangeOn Error Resume NextxTitleId = “Remove Leading Zeros” Set Work_Range
=Application.SelectionSet Work_Range = Application.
InputBox(“Range”, xTitleId, Work_Range.Address, Type:=8)Work_Range.
NumberFormat = “General”Work_Range.Value = Work_Range.Value
End Sub
Using this code, you can easily convert the number values to the General number format. And the General format directs you toward removing the leading zeros. If you want to end up the procedure instantly, you can run the code from the module window just by pressing the F5 key. You can run the code from the worksheet tabs as well.
Moreover, you can even add a button for the macro to run the code with just a single click.
Close the Visual Basic window and open the Developer tab.
Choose the Controls group and press the Insert button. Now click on the Button given in the form Control menu.
You will see the Macro dialog box pops up so that you can give macro to the button.
Now, select the related Macro and you can remove the leading zeros. Select Ok.
The cursor can change into a cross-sign pointer for the button.
Open and drag the cross pointer to form the button in the area.
Select the cells containing number values to run the code and click on the added Remove Leading Zeros.
Click OK to confirm the selection.
The button can run the code with the Visual Basic Editor.
Final Thoughts
That’s it!
You are now aware of the methods to remove leading zeros in Excel. Try any of the above-mentioned methods and you will be able to perform this task smoothly.
