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.

1. Using the 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. Removing 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. Using Custom Number Format to Remove Leading 0 from Excel

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. Applying the 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. Combining Excel Functions to Remove Leading 0 from Excel

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.

7. Using Excel VBA to Delete Leading 0

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

Download Practice Workbook

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

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.

<< Go Back To Data Cleaning in Excel | Learn Excel