In this article, I will show you a good number of techniques on how to remove leading zeros in Excel. We don’t create all our worksheets ourselves. Sometimes we deal with poorly formatted worksheets where you might find numbers are formatted as text. In the text formats, numbers will show the leading zeros.

**Table of Contents**hide

## Download the working file

## How to remove leading zeros in Excel

### 1) Converting text to the number (the most convenient way)

This is the easiest way. Choose those cells that are showing numbers with leading zeros.

Click on the little yellow icon that appears when you select the cell range.

A drop-down menu will appear with several options there. Choose the **Convert to Number** option.

You see all the numbers are converted to numbers and with no leading zeros.

In this article, you will learn a good number of techniques on how to convert text to numbers in Excel.

### 2) Using Excel’s VALUE () function

You can also use Excel’s **VALUE** function. If numbers are formatted as text and you use the VALUE function on them, you will get the numbers.

Take a look at the following image, you will get an idea of how the Excel VALUE function works.

Let’s now apply the VALUE function on our text formatted numbers with leading zeros.

I input a value formula in cell **B2** as the following image.

I use the formula for all other cells in the column and here is the result.

### 3) Using VALUE and TEXT functions together

VALUE function will change the text formatted numbers to numbers, but what if you want to remove the leading zeros but want to keep the text formatted numbers in the text format. You have to use TEXT and VALUE functions together.

Use this formula in cell `<code>`

**B2: =TEXT(VALUE(A2), “#”)**

You see the results here.

VALUE function converts the Text formatted numbers into numbers, but **TEXT** function’s this argument **“#”** converts them texts again.

### 4) Multiplying the column with 1 (useful if you want to keep the numbers in text format)

Make a new column with a multiplying factor (in this case 1). And here is the result.

**Note:**After using the formula for once, the cell will be formatted as text. So next time, no formulas will work on those cells. To keep the formula working again, change the format of the cells to

**General**.

### 5) Using Paste Special Command

You can also use **Paste Special** command to remove the leading zeros.

In a cell, say in cell **C2**, put 1 numeric value and copy the value. As shown in the following image.

Now select those cells that have the text formatted numbers => Right-click on the selection => And click on the **Paste Special** command.

**Paste Special** dialog box appears and choose **Multiply** option from this dialog box and then click **OK**.

And here is the result.

### 6) Using Excel’s Text to Columns feature

We can also use the Text to Columns feature in Excel to remove leading zeros from text-formatted numbers.

Select the cells => **Data** tab => **Convert Text to Column Wizard** opens => Click on **Next**.

As it is. Click **Next** again.

I change the **Destination** cell in this step. And click on the **Finish** button.

**Destination**and it was

**$A$2**.

And this is the result we get.

### 7) Removing leading zeros from numbers with Excel VBA

I shall use a code to delete leading zeros from a cell or a range.

This is the code that I have used to delete leading zeros from numbers.

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

#### # Where will you paste this code?

At first, open the Excel VBA editor. **Developer** Tab => **Code** window => **Visual Basic** command. Or just press **ALT + F11** keys together on your keyboard to open the VBA editor.

And create a new **Module** from the **Insert** tab of the **VBA Editor**.

Now copy-paste the above code in the **Module**.

#### # How to add this Macro to a Command Form Control?

Go to the **Developer** tab => **Controls** window => Click on the **Insert** drop-down => Choose the **Button** from the **Form Controls** part.

Try to draw a button as shown in the image below.

Assign Macro dialog box will be available.

In the dialog box, select the Delete_Leading_Zero macro for the button and click OK.

By default, the command button will be named with a generic name. We just use a name suitable for the Macro.

This is the final thing.

## How to remove leading zeros from non-numeric values?

This is a special case. See the following worksheet.

All these texts have 9 characters and we want to remove only the leading zeros from these texts.

In cell B2, I use this formula:

**=IF(LEFT(A2) = “0”, RIGHT(A2, LEN(A2)-1), A2)**

This formula will remove the first leading zero from the text. To remove other leading zeros from this text, I extend this formula to the right-side cells (up to J column as at most 9 leading zeros can be in a text). And you see the results.

Now just copy these formulas for other rows of the range. And you get your desired results in the last column of this range.

Quite tricky, right? I learned this technique from this page.

## Conclusion

So, these are my ways of removing leading zeros from Excel numbers that are formatted as text. Do you know any other technique? Please let us know in the comment box.

## Further Readings

- 4 Easy Ways to Add Leading Zeros in Excel
- Add or Keep Leading Zeros in Excel (10 Suitable Ways)
- How to Round Off Numbers in Excel (4 Easy Ways)
- Excel 2 Decimal Places without Rounding (4 Efficient Ways)
- Excel round to nearest 10000 (5 Easiest Ways)
- How to Round up Decimals in Excel (4 Simple Ways)
- How to Round to Nearest Multiple of 5 in Excel (3 Easy Ways)
- Excel Custom Number Format Multiple Conditions
- How to Round Numbers in Excel Without Formula (3 smart ways)
- How to Apply Accounting Number Format in Excel! (Easy way)

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