In this article, I will discuss how to remove zeros (0) from Excel. Often, when we work with spreadsheets not prepared by us, we face different types of number formats in cells. For example, phone numbers may contain leading zeroes. On the other hand, some cells may contain only zeros as values which may affect further calculations in excel (For instance, in calculating average). Luckily, Excel has several options to remove both types of zeros. So, let’s go through the methods.

**Table of Contents**hide

**Download the Practice Workbook**

You can download the practice workbook that we have used to prepare this article.

**7 Easy Methods to Remove 0 from Excel**

**1. Apply Find and Replace Option to delete 0 from Excel**

If we want to delete the zero values from a range of data, **the Find and Replace** option can be a great help. Here are the steps involved:

**Steps:**

- First, select the entire dataset (
**B5:B13**).

- Next, type
**Ctrl+T**from the keyboard.**Find and Replace**window will show up. Now, go to the**Replace**tab, type**0**in the**Find what**field, leave the**Replace with**field blank. Then, put the checkmark on ‘**Match entire cell contents**’ as we are looking for the cells which only contain zeros. Otherwise, it will replace zeros that are located in any number; such as 100, 80, 90, etc. After that, click on the**Replace All**button.

- Excel will show how many zero cell values are replaced with blanks. Hit on the
**OK**button.

- Finally, here is the output; all the zeros have been removed from the dataset.

**2. Remove Leading 0 Using Error Checking Option (Convert Text to Number)**

Sometimes, people apply **Text** format in Excel cells to show the leading zeros. If we want to delete these leading zeros at once, we can convert the **Text** to** Number** with a single click. So, here are the associated steps:

**Steps:**

- First, select the entire dataset (
**B5:B13**) that contains leading zeros. Now, you will notice a yellow icon showing up at the top left corner of the selection.

- Next, click on the yellow error checking icon and choose the ‘
**Convert to Number**’ option from the drop-down.

- Lastly, we will see that all the leading zeroes are gone.

**3. Erase Leading 0 by Changing Custom Number Formatting of the Cells**

Now, we will discuss another method to delete leading zeros. Sometimes, people use custom number formats in datasets. For example, each cell will contain a certain number of digits whatever the value is. In such cases, we can delete leading zeros just by choosing the **General Number** format.

**Steps:**

- Select the entire dataset (
**B5:B11**) at first.

- Next, go to the Number group from the
**Home**By default**,**the**Special**number format is selected here.

- Now, choose
**General**from the drop-down.

- Finally, the following is our output.

**4. Delete Leading 0 by Using Paste Special Technique**

We can delete leading spaces from datasets by using **Paste Special** technique. By default Excel cells **Number** format is **General** and we will apply this principle in this method. This method will work for both the **Custom Number** format and numbers that are converted to **Text**. Let’s assume we have a dataset in which values (numbers) are both in **Text **and **Custom **format.

**Steps:**

- First, select an empty cell and copy the cell.

- Next, select the dataset (
**B5:B13**) and right-click on it and choose the**Paste Special**

- Now, the
**Paste Special**window will show up. Then, select**Add**from the**Options**group and click on the**OK**.

- Finally, the following is our output.

**5. Use VALUE Function to Remove Leading 0 from Excel**

Unlike described in previous methods, now we will discuss how to delete leading spaces using Excel functions such as **the VALUE function**. The **VALUE** function converts a text string that represents a number to a number. Likewise **Method 4**, this formula will work for both the **Custom Number** format and numbers that are converted to **Text**.

**Steps:**

- Type the below formula in
**Cell C5**.

`=VALUE(B5)`

- In the end, we will get the following output. Use the
**Fill Handle**(**+**) tool to copy the formula to the rest of the cells.

**6. Erase Leading 0 from Text in Excel Using Combination of Functions**

Till now, we have discussed how to delete zeros when the cell contains only digits. However, there are situations where cells contain both the text and digits. In such scenarios, we can delete leading zeros using combinations of excel functions. For instance, in this method, we will combine the **RIGHT**, **LEN**, **FIND**,** LEFT**, and **SUBSTITUTE** functions to delete leading zeros.

**Steps:**

- First, type the following formula in
**Cell C5**.

`=RIGHT(B5,LEN(B5)-FIND(LEFT(SUBSTITUTE(B5,"0",""),1),B5)+1)`

- The following will be the output of the above-mentioned formula.

**Breakdown of the Formula:**

➤ **SUBSTITUTE(B5,”0″,””)**

Here, **the SUBSTITUTE function **replaces zeros with **Blank** (“”), the result is ‘**ABCD**’.

➤ **LEFT(SUBSTITUTE(B5,”0″,””),1)**

Here, **the LEFT function** extracts the leftmost character of the string. And, the result is ‘**A**’.

➤ **FIND(LEFT(SUBSTITUTE(B5,”0″,””),1),B5)**

Now, **the FIND function** looks for the left-most character and its position given by the **LEFT** formula. Here, the result of this part of the formula is ‘**3**’.

Next, 1 is added to the result of the **FIND **formula so that we get the whole length of the text string.

And, then, the result of the **FIND** formula is subtracted from the character length given by **the LEN function**.

**➤**** RIGHT(B5,LEN(B5)-FIND(LEFT(SUBSTITUTE(B5,”0″,””),1),B5)+1)**

Finally, **the RIGHT function** extracts the entire text string excluding the leading zeros.

**7. Delete Leading 0 from Excel Using VBA**

We can delete leading zero using **VBA** too. Let’s go through the steps involved in this method.

**Steps:**

- First, select the entire dataset (
**B5:B13**).

- Next, right-click on the corresponding Excel sheet name and choose
**View Code**.

- Now, a code
**Module**will show up. Then write the following code there.

```
Sub RemoveZero()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Excel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
WorkRng.NumberFormat = "General"
WorkRng.Value = WorkRng.Value
End Sub
```

- After that,
**Run**the code.

- Finally, all the leading zeros are gone from the dataset (
**B5:B11**).

**Conclusion**

In the above article, I have tried to discuss the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.