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]

I am not really clear on what you mean.
If you are referring to customer number formatting and you only want it to 1 decimal place here is a slightly modified version of what I normally use.
Try it and if it is not what you want give us some examples of values and how you expect them to look,

_(* #,##0.0_);[Red]_(* (#,##0.0);_(* "-"?_);_(@_)

 

[MKLAQ]

this is what I want

HEADERS.xlsm
	J	K	L	M	N
5	122.00	10.00	-	1,000.00	1,000.00
6	10,000.00	2,000.00	-	200.00	100.00
7	3,000.00	-	-	-	-
8	-	-	-	500.00	-
9	1,000.00	-	7,000.00	600.00	-
10	-	-	-	-	-
RESULT

 

[Alex Blakenburg]

So did you try the custom formatting I provided ?
Your initial example indicated 1 decimal place for 2 decimals change it back to my standard of:

_(* #,##0.00_);[Red]_(* (#,##0.00);_(* "-"??_);_(@_)

 

[MKLAQ]

it doen't accept it

 

[Alex Blakenburg]

So you went into Format Cells > Number > Custom >
And in the Type box pasted in the line from my post ?
Then what happened.

 

[Alex Blakenburg]

I have logged off for the day but I think the Accounting Format will do what you want. Just select Symbol = None

 

[MKLAQ]

Excel can't use the numbers formatting you typed

 

[Alex Blakenburg]

Excel can't use the numbers formatting you typed

Then either try the accounting format or pick a custom format you like and in the 3rd (zero) position enter “-“ including the quotes.
Custom Excel number format

 

[MKLAQ]

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