Dash for 0 in Excel

This article will explain how to replace the 0 with a dash in your cells.

Difference between a number and text

  • A dash is considered as text
  • 0 is a number

This remark is very important . If you replace each 0 with dash manually, you will create confusion between the text and numeric values ​​in the same column

Dash as Text

In this example, we see that the number of numeric values ​​(returns with the COUNT function) doesn't include the dashes. Here, each dash as a Text format.

How to display a dash automatically instead of 0?

To display a dash instead of the number 0, you have to customize the format of your numbers.

  1. First, open the Format number dialog box
  2. Go to the Custom Category
Open Custom format number

How is displayed number in Excel?

Excel offers 4 display for numbers

  • Display of positive numbers
  • Display of negative numbers
  • Number zero
  • Text

Negative numbers

The display of negative numbers can be represented in red with the following code

#,##0.00; [Red] - #,##0.00

Or between parenthesis with the code

#, ##0.00; [Red](#,##0.00)

Negative numbers in red with parenthesis

Change the display of 0

To change a zero to a dash, you have to play on the 3rd argument of the code of a number. So, by adding just a dash between quotation marks in the 3rd parameter, all your 0s will be replaced by dashes.

#,##0.00; [Red] - #,##0.00 ;"-"

Format code to display dash for 0

Zero are now displayed with a dash and the COUNT function returns 8 numeric values ​​for each columns 😀

The Dash for 0

Related posts


Permanent link to this article: https://www.excel-exercise.com/dash-for-0-in-excel/

Leave a Reply

Your email address will not be published.