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

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.

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

• Finally, you will get the desired result.

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

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

## 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)`

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

## 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), "#")`

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

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

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

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

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

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

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

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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

1. Very good, congratulations

• FERREIRA, Thanks for your feedback!

2. Hi Kawser, Thanks for the lessons

• Hello Johmono,
You’re most welcome.