How to Remove Leading Zeros in Excel (8 Easy Methods)

This tutorial will demonstrate how to remove leading zeros in excel. Leading zeros are those zeros that are positioned at the start of the number but have no value. When we are dealing with lots of data sometimes unnecessary zeros appear unexpectedly. This creates a lot of data storage and confusion. Moreover, these leading zeros have no extra meaning. So, we want to learn how to remove these leading zeros in excel.

Download Practice Workbook

You can download the practice workbook from here.

8 Easy Methods to Remove Leading Zeros in Excel

We will try to remove leading zeros in excel in 8 different suitable ways. If you follow the steps correctly, you should learn how to remove leading zeros in excel on your own. The steps are:

1. Converting Text to Number

In this case, our goal is to remove leading zeros in the excel file by converting text to a number option. Sometimes the numbers in your worksheet don’t act like numbers; they don’t execute any kind of arithmetic operations as they should, and they even may produce errors. The reason for this is, even though they look like numbers, they are actually formatted as text. We will use this Converting Text to Number option to fulfill our goal. We can fulfill our goal by following the below steps.

Steps:

• First, arrange a dataset like the below image.

• Next, select the Convert to Number option from the error mark.

• Finally, you will get the desired result.

Read More: How to Custom Cell Format Number with Text in Excel (4 Ways)

2. Use of Custom Number Formatting

At this point, we will aim to remove leading zeros by the use of the custom number formatting system. We can fulfill our goal by following the below steps.

Steps:

• To begin with, arrange a dataset like the below image.

• In addition, copy the full Column B using the Ctrl+C buttons and paste it to Column D using the Ctrl+V option.

• Furthermore, go to select the desired column > Home > General options.

• Finally, you will get the desired result.

Read More: How to Format Number with VBA in Excel (3 Methods)

3. Using VALUE Function

Now, we want to remove leading zeros using the VALUE function. The VALUE function is categorized under the TEXT functions in Excel. It converts a given text string that represents a number into a numeric value. We can fulfill our goal by following the below steps.

Steps:

• At first, arrange a dataset like the below image, and in the C5 cell insert the following formula.

• Second, if you press the Enter button, you will get the result for the cell and then use the Fill Handle to apply the formula to all the desired cells.

• Last, you will get the desired result.

Read More: [Fixed!] Excel Not Recognizing Numbers in Cells (3 Techniques)

4. Combining VALUE and TEXT Functions

At this point, we will aim to remove leading zeros using a combination of VALUE and TEXT Functions in excel. To keep or add leading zeros in a numerical value, the TEXT function can serve the purpose with the most suitable formula. In the following dataset, assuming that we want to resize all numbers to display a similar size by adding leading zeros before the numbers. We can fulfill our goal by following the below steps.

Steps:

• To begin with, arrange a dataset like the below image, and in the C5 cell insert the following formula.

• In addition, if you press the Enter button, you will get the result for the cell and then use the Fill Handle to apply the formula to all the desired cells.

• Finally, you will get the desired result.

Read More: How to Keep Leading Zeros in Excel (10 Suitable Methods)

5. Multiplying Column with 1

Next, we aim to remove leading zeros multiplying with 1. Some simple tricky mathematical operations can be used to fix the Convert to Number error. The trick is- just multiply or divide the values by 1. Also, you can add zero with the values and it will give you the same output. Here we’ll use the helper column too. We can fulfill our goal by following the below steps.

Steps:

• Firstly, arrange a dataset like the below image.

• Secondly, insert 1 in all the cells of column C.

• Thirdly, in the D5 cell insert the following formula.

• Fourthly, if you press the Enter button, you will get the result for the cell and then use the Fill Handle to apply the formula to all the desired cells.

• Lastly, you will get the desired result.

Read More: How to Round Numbers to the Nearest Multiple of 5 in Excel

Similar Readings

• How to Change Number Format from Comma to Dot in Excel (5 Ways)
• Custom Number Format: Millions with One Decimal in Excel (6 Ways)
• How to Format Number to Millions in Excel (6 Ways)
• Excel 2 Decimal Places without Rounding (4 Efficient Ways)
• How to Convert Number to Percentage in Excel (3 Quick Ways)

6. Using Paste Special Command

In this case, our goal is to remove leading zeros in the excel file by using the Paste Special Command. Paste Special is a feature that gives you more control over deciding how the content is displayed. For example, text copied from a web page often retains much of or all of the HTML formatting. By using Paste Special, you can choose the paste unformatted text. With Paste Special, you can copy not only data but also the source column’s width into the target columns. You can even use Paste Special to perform calculations like addition, subtraction, multiplication, and division. They are still other powerful ways to use Excel’s Paste Special Command. We can fulfill our goal by following the below steps.

Steps:

• First, arrange a dataset like the below image, and in the C5 cell, insert 1 and copy it using Ctrl + C buttons.

• Next, right-click on the desired column and select the Paste Special option.

• After that, the Paste Special dialog box will open on the screen.
• Then, select the Multiply option and click OK.

• Finally, you will get the desired result.

Read More: Excel Number Format Not Working (2 Reasons with Solutions)

7. Use of Text to Columns Feature

Now, we want to remove leading zeros by using the text-to-columns feature in excel. We can fulfill our goal by following the below steps.

Steps:

• At first, arrange a dataset like the below image and go to select the desired cell > Data > Text to Columns options.

• Second, in the 1st dialog box section press the Next option.

• Third, again in the 2nd dialog box section press the Next option.

• Fourth, select the desired column in the destination for the n 3rd dialog box section and press the Next option.

• Last, you will get the desired result.

Read More: [Solved] Excel Number Stored As Text 

8. Applying VBA Code

At this point, we will aim to remove leading zeros by applying the VBA code. Visual Basic for Applications (VBA) macro language is a key component. Excel users use VBA macros to customize and automate Excel. We can fulfill our goal by following the below steps.

Steps:

• To begin with, go to Developer > Insert > Form Controls options.

• In addition, you will get the below result.

• Furthermore, right-click on the button option and select the Assign Macro option.

• Afterward, insert the VBA code here.

Sub Removing_Leading_Zero()
'Code by ExcelDemy.com
Dim Remove_Range As Range
Dim Wrk_Rng As Range
On Error Resume Next
xTitleId = "ExcelDemy Delete Leading Zeros"
Set Wrk_Rng = Application.Selection
Set Wrk_Rng = Application.InputBox("Range", xTitleId, Wrk_Rng.Address, Type:=8)
Wrk_Rng.NumberFormat = "General"
Wrk_Rng.Value = Wrk_Rng.Value
End Sub

• Next, select the desired data range and press the button option.

• Finally, you will get the desired result.

Read More: How to Use Number Format Code in Excel (13 Ways)

How to Remove Leading Zeros from Non-Numeric Values in Excel

In this case, our goal is to remove leading zeros from non-numeric values. Non-numeric means it will contain some texts or anything that is not a number. We can fulfill our goal by following the below steps.

Steps:

• At first, arrange a dataset like the below image.

• Second, in the C5 cell insert the following formula.

• Third, if you press the Enter button, you will get the result for the cell and then use the Fill Handle to apply the formula to all the desired cells.

• Forth, you will get the result for this column.

• Last, if you apply the steps for column D then you will get the desired result.

Conclusion

Henceforth, follow the above-described methods. Hopefully, these methods will help you to remove leading zeros in excel. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our level best to solve the problem or work with your suggestions.

Related Articles

• How to Apply Accounting Number Format in Excel (4 Useful Methods)
• Change International Number Format in Excel (4 Examples)
• How to Enter 16 Digit Number in Excel (3 Simple Ways)
• Display Long Numbers in Excel (4 Easy Ways)
• How to Format a Number in Thousands K and Millions M in Excel (4 Ways)