How to Remove 0 from Excel (7 Methods)

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.

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.

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

• 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 Range As Range
Dim WorkRange As Range
On Error Resume Next
xTitleId = "Excel"
Set WorkRange = Application.Selection
Set WorkRange = Application.InputBox("Range", xTitleId, WorkRange.Address, Type:=8)
WorkRange.NumberFormat = "General"
WorkRange.Value = WorkRange.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.