Hi, I think I get what you're saying, but not sure what answer is.. right
now.. anyways 1st looking for result of view 1B, 2nd choice of 1.5B (or M).
Formula does what you asked, negates right most character.
Sounds like I cannot do what needing to do.
Might repost sometime to see if any ideas, but looks like I am in a narrow
area for something that might not have been thought of. Thanks.

"Sandy Mann" wrote:

nastech

In your formula does the LEFT and RIGHT Functions refer to the B in 1.511B?
If so then formatting will not work with it because the B are not real
letters that can be tested for. For example a custom format of "0.00B"
(with out the quotes) and with 123 entered in the cell will show as 123.00B
Testing it with =RIGHT(A1,1) will return 3 because the .00B are not real.
The only thing in the cell is the 123 you entered in the first place.

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"nastech" wrote in message
...
Hi, I am looking for way to custom format cell to read 1.511B or 1.511M
as: 1B 1M (also would like example for 1.5B or 1.5M). thanks

already have cell formatted as Number, works with other work column with
e.g.:
=IF(OR(CE138=0,BZ138={0,"n/a"}),"",(LEFT(BZ138,LEN(BZ138)-1)*IF(RIGHT(BZ138,1)="b",10^9,1)/CE138*$BV$4))

Just looking for custom formatting such as: ##0.0*