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.

**Table of Contents**hide

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

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

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

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

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

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

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

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

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

Very good, congratulations

FERREIRA, Thanks for your feedback!

Hi Kawser, Thanks for the lessons

Hello Johmono,

You’re most welcome.

Thanks for your feedback.

Best regards