How to Remove 0 from Excel (7 Methods)

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. Here’s an overview of one of the options.

Method 1 – Using the Find and Replace Option to Delete 0 Values from Excel

Steps:

Select the entire dataset (B5:B13).

Type Ctrl+T from the keyboard. Find and Replace window will show up.
Go to the Replace tab, type 0 in the Find what field, and leave the Replace with field blank.
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.
Click on the Replace All button.

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

Here is the output where all the zeros have been removed from the dataset.

Method 2 – Removing Leading 0 Using Error Checking Option (Convert Text to Number)

Steps:

Select the entire dataset (B5:B13) that contains leading zeros. You will notice a yellow icon showing up at the top left corner of the selection.

Click on the yellow error checking icon and choose the ‘Convert to Number’ option from the drop-down.

We will see that all the leading zeroes are gone.

Method 3 – Using Custom Number Format to Remove Leading 0 from Excel

Steps:

Select the entire dataset (B5:B11).

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

Choose General from the drop-down.

The following is our output.

Method 4 – Delete Leading 0 by Using Paste Special

Let’s assume we have a dataset in which values (numbers) are both in Text and Custom format.

Steps:

Select an empty cell and copy the cell.

Select the dataset (B5:B13) and right-click on it, then choose Paste Special

The Paste Special window will show up. Select Add from the Options group and click on OK.

This converts the text into numbers, resulting in removing leading 0s.

Method 5 – Applying the VALUE Function to Remove Leading 0 from Excel

Steps:

Copy this formula in Cell C5:

=VALUE(B5)

Press Enter to get the first output.
Use the Fill Handle (+) tool to copy the formula to the rest of the cells.

Method 6 – Combining Excel Functions to Remove Leading 0 from String in Excel

Steps:

Copy the following formula in Cell C5:

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

Here’s the output of the formula once you use AutoFill.

Formula Breakdown

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

Method 7 – Using Excel VBA to Delete Leading 0

Steps:

Select the entire dataset (B5:B13).

Right-click on the corresponding Excel sheet name and choose View Code.

A code Module will show up. Copy 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