This post will guide you how to replicate zeros with blank cells in a selected range in Excel. How do I convert cells containing zero with blank cells  in selected range in an Excel Worksheet.

Assuming that you have a list of data in range A1:D3 which contain some String values, and you want to convert cell which contain zero values to blank cells in those cells. And this post will show you tow methods to replace zeros to blank cells.

1. Replace Zeros with Blank Cells with Find and Replace Function

To replace cells that contain zero values to Blank, you can find all zero cells firstly, and replace all searched cells with blank character. Just do the following steps:

Step1: go to Home tab in the Excel Ribbon, and click Find & Select command under Editing group, then click Replace option from the drop down menu list or press Ctrl +F keys on your keyboard, and the Find And Replace dialog will open.

Step2: enter number 0 into the Find what text box, and type a space character into the Replace with text box in the Find and Replace dialog box, then click Options button to expand the advance options, check Match entire cell contents checkbox.

Step3: click Replace All button in the Find and Replace dialog box, then a dialog box will open to tell you how many cells has been replaced.

Step4: click Ok button. You would see that all cell which contain zero values have been replaced with blank character.

Step5: click Close button to close the Find and Replace dialog box.

2. Replace Zeros with Blank Cells with VBA Macro

You can also use an Excel VBA Macro to achieve the same result of replacing zeros with blank in the selected range of cells. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

Sub ReplaceZerowithBlank()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select One Range That You Want to Convert:", "ReplaceZerowithBlank", myRange.Address, Type:=8)
    For Each oneCell In myRange
        If oneCell.Value = 0 Then
            oneCell.Value = ""
        End If
    Next
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

Step6: Select One Range That You Want to Convert. Click Ok button.

Step7: Let’s see the result:

3. Video: Replace Zeros with Blank Cells

This Excel video tutorial will show you how to replace zeros with blank cells in Excel. We’ll explore two efficient methods: using Excel’s Find and Replace function for a manual approach and employing a VBA Macro for an automated solution to clean up your data quickly.