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

Get FREE Advanced Excel Exercises with Solutions!

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.

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 to Remove Leading Zeros in Excel

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.

Dataset to Remove Leading Zeros in Excel

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

Error option to Remove Leading Zeros in Excel

  • Finally, you will get the desired result.

Final Result to Remove Leading Zeros in Excel


2. Using of Custom Number Format in Excel to Remove Leading Zeros

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.

Dataset to Remove Leading Zeros in Excel

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

Copying to Remove Leading Zeros in Excel

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

  • Finally, you will get the desired result.


3. Inserting Excel VALUE Function to Remove Leading Zeros

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.
=VALUE(B5)

Inserting Formula to Remove Leading Zeros in Excel

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

Using Fill Handle to Remove Leading Zeros in Excel

  • Last, you will get the desired result.

Showing result to Remove Leading Zeros in Excel


4. Combining VALUE and TEXT Functions to Remove Leading Zeros

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.
=TEXT(VALUE(B5), "#")

Inserting Formula to Remove Leading Zeros in Excel

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

Using Fill Hanle to Remove Leading Zeros in Excel

  • Finally, you will get the desired result.

🔎 How Does the Formula Work?

  • VALUE(B5): This portion represents the cell you want to convert.
  • TEXT(VALUE(B5), “#”): This portion represents the whole condition according to our wish.

Read More: How to Keep Leading Zeros in Excel


5. Removing Leading Zeros by 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.

Dataset to Remove Leading Zeros in Excel

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

  • Thirdly, in the D5 cell insert the following formula.
=B5*C5

Inserting Formula to Remove Leading Zeros in Excel

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


6. Using Excel Paste Special Command to Remove Leading Zeros

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. There 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: How to Remove Zeros in Front of a Number in Excel


7. Removing Leading Zeros with Excel 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:

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


8. Applying Excel VBA Tool for Removing Leading Zeros

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.


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:

  • First, arrange a dataset like the below image.

  • Second, in the C5 cell insert the following formula.
=IF(LEFT(B5) = "0", RIGHT(B5, LEN(B5)-1), B5)

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

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

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

Read More: [Solved]: Leading Zero Not Showing in Excel


Download Practice Workbook

You can download the practice workbook from here.


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


<< Go Back to Pad Zeros in Excel | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

4 Comments
  1. Very good, congratulations

  2. Hi Kawser, Thanks for the lessons

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo