How to Convert Zeros to Blank in a Selected Range in Excel?

In Excel, you may need to convert zeros to blank cells at times. If we attempt to complete the task manually, it can be a time-consuming process. Read this tutorial to learn how you can convert zeros to blanks in a selected range in Excel. The blank represents the value of being empty and may or may not be filled in the future. Sometimes zeros can also be considered a value. We can complete the process using the Find and Replace and VBA applications.

Converting Zeros to Blank in Excel

Here we will first open the find and replace function, then replace the zeros with blanks. Let's look at a simple procedure for converting zero to blank in Excel using find and replace. We can use this process if we need to apply it to the whole worksheet.

Step 1

Let us consider an excel sheet where the data is a list of numbers including some zeros, similar to the below image.

Now, press CTRL + F to open find and replace.

Step 2

Now in the pop-up, click on Replace, then in Find, enter 0, and in Replace, enter Space.

Replace > 0 > > Replace all

If we need to apply the process to a selected cell, we can use the help of a VBA application.

Converting Zero to Blank in a Selected Range

Here we will first open the VBA application, then enter and copy the code into the textbox. Let's look at a simple procedure for converting zero to blank in an Excel range.

Step 1

Let us consider the same data that we used in the above example.

Now right-click on the sheet name and select view code to open the vba application, then click on insert and select module.

Then type the below-mentioned programme in the text box as shown in the below image.

Right click > View code > Insert > Module > programme

Program

Sub ChangeZero()
'Updated By Nirmal
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Convert Blank cells"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    If Rng.Value = 0 Then
        Rng.Value = ""
    End If
Next
End Sub 

Step 2

Then save the sheet as a macro-enabled workbook, click F5 to run the code, select the range of cells you want to change to blank, and click OK, as shown in the below image.

Save > F5 > Select range > OK

Conclusion

In this tutorial, we used a simple example to demonstrate how we can convert zeros to blanks in Excel.