Home » Forums » Open Source Learning Center » Question and Answer

how to change zero to dash?

Submitted by mampwamp2 on 8 January, 2013 - 11:44

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?

‹ How to break a string into substrings and save substrings into array? Changing the Background color ›
»
Almir's picture

Several ways to accomplish this...

Submitted by Almir on 10 January, 2013 - 09:36.

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

Submitted by Nick on 9 January, 2013 - 16:37.

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"

Submitted by jangirpa on 9 January, 2013 - 16:16.

Try using "SUBSTITUTE" function.

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

»