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.
- Replace Zeros with Blank Cells with Find and Replace Function
- Replace Zeros with Blank Cells with VBA Macro
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.
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.
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: