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

Apply Find and Replace Option to delete 0 from Excel

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

Apply Find and Replace Option to delete 0 from Excel

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

Apply Find and Replace Option to delete 0 from Excel

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

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

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

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

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

Erase Leading 0 by Changing Custom Number Formatting of the Cells

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

Erase Leading 0 by Changing Custom Number Formatting of the Cells

  • Now, choose General from the drop-down.

Erase Leading 0 by Changing Custom Number Formatting of the Cells

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

Delete Leading 0 by Using Paste Special Technique 

Steps:

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

Delete Leading 0 by Using Paste Special Technique 

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

Delete Leading 0 by Using Paste Special Technique 

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

Delete Leading 0 by Using Paste Special Technique 

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

Use VALUE Function to Remove Leading 0 from Excel

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

Erase Leading 0 from Text in Excel Using Combination of Functions

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

Delete Leading 0 from Excel Using VBA

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

Delete Leading 0 from Excel Using VBA

  • 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

Delete Leading 0 from Excel Using VBA

  • After that, Run the code.

Delete Leading 0 from Excel Using VBA

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

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo