How to Replace Zeros with Blank Cells in Excel

,

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.

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.

relace zeros with blank1

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.

relace zeros with blank2

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.

relace zeros with blank4

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

relace zeros with blank5

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

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.

Get the position of the nth using excel vba1
Step2: then the “Visual Basic Editor” window will appear.

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

export each sheet to csv2

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

relace zeros with blank6

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.

relace zeros with blank7

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

relace zeros with blank8

Step7: Let’s see the result:

relace zeros with blank5

Leave a Reply