how to change zero to dash?

how to change zero to dash?
some of the excel table information have no information, so i insert zero for that column. can it automatic change to dash?

Almir's picture

Several ways to accomplish this...

Format to "Comma Style" and enter zeros. This is just formatting, meaning cell value remains zero, but it is shown as dash. If you need exact value to be dash, try replace blanks with dashes. Check "Match Entire Cell Contents" box in Options of replace window.

Nick's picture

David Frew: Use custom

David Frew: Use custom formatting: #,##0;[red](#,##0);"-";"??"

First parameter is +ve number
2nd is negative (which I've shown in red with brackets instead of "-")
3rd is for your zero value
4th is for unset (or Null) but you don't have to use it (default is blank)

Try using "SUBSTITUTE"

Try using "SUBSTITUTE" function.

e.g. SUBSTITUTE(A1,"0","")