Now, that you have selected all the cells having 0 values, you can delete the zeros, replace zeros with any character or highlight cells having zeros. Hold the Control Key and Press the A Key to select all the cells that have 0 in them. Once you click on Find All, the cells with zero values will become highlighted.Ħ. In the expanded ‘Find and Replace’ box, select Match entire cell contents option and click on Find All button.ĥ. Select the Entire Data having Zero valuesĤ. In case you do not want to remove the zeros right away, you can first find zeros in the data field, select all zeros and deal with them as required.ġ. Note: If you want to show ‘NA’ in regular colour, simply type 0 -0 ”NA”.
Once you click on OK, all the Cells with 0 values will be showing the text ‘NA’ in red colour.
In the right-pane, enter 0 -0 ”NA” in the ‘Type’ field. In Format Cells dialog box, click on the Number tab and select Custom option in left-pane. Select the Entire Data in which you want to replace zeros with Text.ģ. For example, you can replace zeros with ‘NA’, ‘Not Available’ or any other text.ġ. You can also replace zeros with any text field in Excel. Also, if you enter zero in any cell, it will be replaced with Dash (-). Once you click on OK, Cells with 0 values will be showing a dash (–). Click on OK to save and implement this change. In the right-pane, enter 0 -0 ”–” in the ‘Type’ field.Ĥ. Click on the Home tab > select Format option in ‘Cells’ group > click on Format Cells… in the drop-down menu.ģ. Select the Entire Data in which you want to replace zeros with dash.Ģ. Replace Zeros with Dash in ExcelĪnother way of dealing with zero values in Excel is to replace zeros with dash (-). This can be achieved using custom number formatting option as available in Excel.ġ. Once you click on ‘Replace All’ button, all the cells in your data having zeros will immediately become blank. To call the method SearchAndReplace, you can use the following example.
#Excel search and replace text how to#
In the expanded ‘Find and Replace’ box, select Match entire cell contents option and click on Replace All button. This example also shows how to use a regular expression to search and replace the text value, 'Hello world' stored in a word processing file named 'MyPkg8.docx,' with the value 'Hi Everyone'. In ‘Find and Replace’ dialog box, enter 0 in ‘Find what’ Field > leave the ‘Replace with’ field empty (enter nothing in it) and click on Options.Ĥ. Click on the Home tab > click on Find & Select in ‘Editing’ section and select the Replace option in the drop-down menu.ģ. Select the Entire Data in which you want to replace Zeros with blank cells.Ģ. Replace Zeros With Blank in Excelįollow the steps below to replace zeros with blanks in Excel.ġ. With this understanding, let us go ahead and take a look at the steps to replace zeros with blank, dash or text field in Excel.
If you replace zeros with Dash (-) or a Text in Excel, you may sometimes encounter errors, if cells with Dash or Text Field are referenced in certain formulas.