Custom Format syntax Q
 

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

samples how received:

1.551M
..071B

Custom Format syntax Q
 

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)

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

samples how received:

1.551M
.071B

Custom Format syntax Q
 

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

"Bob Phillips" wrote:

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)

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

samples how received:

1.551M
.071B

Custom Format syntax Q
 

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))+R OUND(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)

"nastech" wrote in message
...
hi, want to see 1.511B as 1B (would also like to see as 1.5B). thanks

"Bob Phillips" wrote:

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)

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

samples how received:

1.551M
.071B

Custom Format syntax Q
 

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*


"Bob Phillips" wrote:

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))+R OUND(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)

"nastech" wrote in message
...
hi, want to see 1.511B as 1B (would also like to see as 1.5B). thanks

"Bob Phillips" wrote:

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)

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

samples how received:

1.551M
.071B