How to replace zeros with space, dash or text in Excel

Sometimes the presence of zeros in the Excel data field can make histograms and other types of charts look inaccurate. Below are the steps to replace zeros with space, dash or text in Excel.

How to replace zeros with spaces in Excel

Before proceeding with the steps on how to replace zeros with spaces in Excel, you need to understand that replacing zeros with spaces will result in removing the data from cells with zero values.

When you remove zeros in Excel, all cells containing zeros lose their data and are treated as blank cells with no data.

When you replace zeros with a hyphen (-) or text in Excel, you may sometimes encounter an error when hyphenated cells or text boxes are referenced in certain formulas.

Let’s continue with this understanding and take a look at the steps to replace zeros with spaces, dashes or text boxes in Excel.

1. Replace zeros with spaces in Excel

Follow the steps below to replace zeros with spaces in Excel.

1. Choose Entire data where you want to replace zeros with empty cells.

2. Click on that home tab > click Search & select in the Edit section and select the Substitute option in the drop down menu.

Find and Replace option in Excel

3. In the Find and Replace dialog box, type 0 in the “Find what” field > exit the “Replace with” field. empty (do not enter anything) and click options.

Open the Find and Replace option

4. Select “Find and Replace” in the expanded field Match entire cell content option and click Replace everything Button.

Replace all zeros with spaces in Excel

Once you click the “Replace All” button, all cells in your data with zeros will become blank immediately.

2. Replace zeros with dashes in Excel

Another way to deal with null values ​​in Excel is to replace zeros with dashes (-). This can be achieved using the custom formatting option for numbers as available in Excel.

1. Choose Entire data where you want to replace zeros with dashes.

2. Click on that home Tab > select format option in the Cells group > click Format Cells… in the drop down menu.

Format Cells option in Excel

3. In the Format Cells dialog box, click number tap and select custom option in the left pane. In the right pane, type 0;-0;”–” in the “Type” field.

Replace zeros with dashes in Excel

4. Click on OK to save and implement this change.

Once you click OK, cells with 0 values ​​are displayed with a dash (–). If you type a zero in any cell, it will be replaced with a hyphen (-).

3. Replace zeros with text in Excel

You can also replace zeros with any text box in Excel. For example, you can replace zeros with “NA”, “Not available” or any other text.

1. Choose Entire data where you want to replace zeros with text.

2. Click on that home Tab > select format option in the Cells group > click Format Cells… in the drop down menu.

Format Cells option in Excel

3. In the Format Cells dialog box, click number tap and select custom option in the left pane. In the right pane, type 0;-0;[Red]”N / A” in the “Type” field.

Replace zeros with text in Excel

4. Click on OK to save and implement this change.

Once you click OK, all cells with 0 values ​​show the text “NA” in red color.

Note: If you want to show ‘NA’ in normal color, just type 0;-0;”NA”.

4. Find zeros in Excel

If you don’t want to remove the zeros right away, you can first search for zeros in the data field, select all zeros, and edit as needed.

1. Select the whole Data have null values

2. Click on that home tab > click Search & select in the Edit section and select the Substitute option in the drop down menu.

Find and Replace option in Excel

3. In the Find and Replace dialog box, type 0 in the “Find what” field > exit the “Replace with” field. empty (do not enter anything) and click options.

Open the Find and Replace option

4. Select “Find and Replace” in the expanded field Match entire cell content option and click find all Button.

Find all zeros in Excel

5. Once you click Find All, the cells with zero values ​​will be highlighted.

6. Shut control key and press the A key to select all cells containing 0.

Now that you have selected all the cells with 0 values, you can delete the zeros, replace zeros with any character, or highlight cells with zeros.

Related

Leave a Reply

Your email address will not be published.