Custom number format zero not showing

[Ironman]

Hi

I'm using a custom number format #"lb" so it looks like the below. But I just can't figure out how to amend it so where the value is zero, it shows "0lb", not "lb".

-3lb
-15lb
lb

Help appreciated, thank you!

 

[JoeMo]

Try 0"lb"

 

[Ironman]

Ahhh that's brilliant, many thanks Joe!

 

[JoeMo]

Ahhh that's brilliant, many thanks Joe!

You are welcome - thanks for the reply.

 

[Ironman]

In the above example, how do I format a cell so only positive values are prefixed "+(x)lbs"? I've tried +0"lb" but that returns a plus prefix for negative and zero values as well.

 

[JoeMo]

In the above example, how do I format a cell so only positive values are prefixed "+(x)lbs"? I've tried +0"lb" but that returns a plus prefix for negative and zero values as well.

If you mean something like this:

Book1
	F
1	3
2	5lbs
3	0lbs
Sheet1

NOTE: if you mouse over the 3 in F1, you'll see it has a value of -3.
try:
0"lb";0;0"lb"

If you don't want 0 to have a "lb" suffix, drop the "lb" on the last element.

 

[Ironman]

Thanks again Joe, I was looking for this (the 5lbs cell is the only positive value) without the +signs after the negative values/preceding the zero value:

+5lbs
-+3lbs
-+15lbs
+0lbs

 

[Rick Rothstein]

Does this Custom Format do what you want...

+0"lb";-0"lb";0"lb"

 

[Ironman]

Brilliant Rick, works perfectly! Thanks a lot.

Thanks to you too Joe for your earlier solution.