Convert zero to hyphen"-" and number formatting in cells for multiple columns at the same time

[MKLAQ]

hi experts

I use normal way bye cell format by choose the cells and customize to make any value = 0 should convert to "-" and when make the cells contain numbers. it should show the numberformat #,#00.0 so far is ok , but if I use customize to make any value = 0 should convert to "-" .it will clear number format then the cells it will be like this ##### without any numberfromat . the question is. is there any macro or way to make value = 0 convert to "-" show the numberformat #,#00.0 at the same time without return the normal value .for instance I have columns B,F,H,M,N contains some numbers and zero .

I appreciate if anybody solve this dilemma

thanks in advance

 

[Alex Blakenburg]

thanks . is there way to make the empty cell conatain "-"?
the empty cell or cell contain 0 should convert to "-"

I don't believe there is a way of making an empty cell display "-".
If you are populating the column with a formula and are returning "" then change that to a zero.
In a pivot table it will also effectively convert to zero and the number formatting will apply to it.

 

[bebo021999]

Manually solution:
Select range of data
Ctr-G (GoTo), Select Special/ Blanks
Now all Blank Cells in range are hightlied
Ctrl-H( Find And Replace)
Find What: Leave blank
Replace with: -
OK

 

[MKLAQ]

I don't believe there is a way of making an empty cell display "-".

that make me going back in post#4 why doesn't work. do you work for you?

 

[Alex Blakenburg]

I don't know why my post #4 is not working for you unless it is the Rich text or that I modified it to 1 decimal manually.
Here it is again with 2 decimlas if you want to try copy pasting it into the custom box
_(* #,##0.00_);[Red]_(* (#,##0.00);_(* "-"??_);_(@_)

It might be overkill for what you need.
Here is the breakdown with examples.

I can see no way of formatting an empty cell as "-" though.