Custom Format syntax Q

[Guest]

Hi, tyrying to format cells so combinations of following look like:
1M 15B (possibly 1.5M) thanks.

samples how received:

1.551M
..071B

 

[Bob Phillips]

Not clear what you get or what you want

Do you want to see 1,500,000 as 1.5M, 1,000,000,000 as 1B etc

or

1.551M as 1,551,000, etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

hi, want to see 1.511B as 1B (would also like to see as 1.5B). thanks

 

[Bob Phillips]

I think you are going to struggle here, as the 1.511B will be text, and text
cannot be formatted. You could extract it like so

=IF(ISNUMBER(FIND(".",A1)),LEFT(A1,FIND(".",A1))+ROUND(MID(A1,FIND(".",A1),L
EN(A1)-1-FIND(".",A1)+1),1)&RIGHT(A1))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

thankyou that probably would work. I am not that quick at excel, but apply
some time to it, came up with:
=IF(OR(CE138=0,BZ138={0,"n/a"}),"",(LEFT(BZ138,LEN(BZ138)-1)*IF(RIGHT(BZ138,1)="b",10^9,1)/CE138*$BV$4))

Apologize if did not ask question quite right, for cell where item resides
in (e.g. 1.511B, want custom formatting to make appear as either: 1B or
1.5B; as some figures are 1.5M etc., am pretty sure there is some
combination that would do it.
example type: ##,0.0*