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

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

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. 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 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. ### 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. ## 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.
`=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. • 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. ## Related Articles Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

1. Reply Very good, congratulations

• Reply 2. Reply Hi Kawser, Thanks for the lessons

• Reply Hello Johmono,
You’re most welcome.   