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*

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*

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*

alternate (possible) formatting (e.g.) might have been: #,##0? ?
after looking a little more.

"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*

what found so far:
Hi, am looking for way to custom format cell to read 1.511B or 1.511M
as: 1B 1M (both B for billion & M.. present in column, thanks)

may need to use worker column to get rid of unwanted digits?
separate work column might not work for me, but would be:
=TEXT(LEFT(BZ9,LEN(BZ9)-1),"#,##0")&RIGHT(BZ9,1)

Just looking for custom formatting such as: #,##0? ??

Is this an area not possible for custom formatting in Excel?
XXXXXXXXXXXXXXXXXX

"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*

nastech,

Assuming that you mean American Billions not British Billions, the Custom
Format that you are searching for is:

[=1000000000]#,,,"B";[=1000000]#,,"M";General

However this will not do what you want. Try this:

Custom Format A1 with the above custom format, then enter 1000000002 You
should see 1B in the cell as you want.
Now in another cell enter the formula:

=RIGHT(A1,1)

Do you see 'B' ? No! you see the figure 2 because the 'B' does not exist,
it is simply formatting the same as if you format a cell as currency you see
the $ or £ sign in front of the numbers but if you test it with =Right(A1,1)
you get the first number not a dollar sign.

I don't really follow what you are trying to do in your formula but try
something like:

=IF(OR(CE138=0,BZ138={0,"n/a"}),"",IF((LEN(INT(BZ138))9)*ISNUMBER(BZ138),BZ1 38,1/CE138*$BV$4))

I assume that if BZ138 is a billion or more then you want to divide BZ138 by
CE138*$BV$4 otherwise return the reciprocal of CE138*$BV$4

If there is no chance that BZ138 will contain decimals then you can replace
the INT(BZ138) with just BZ138

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"nastech" wrote in message
...
what found so far:
Hi, am looking for way to custom format cell to read 1.511B or 1.511M
as: 1B 1M (both B for billion & M.. present in column, thanks)

may need to use worker column to get rid of unwanted digits?
separate work column might not work for me, but would be:
=TEXT(LEFT(BZ9,LEN(BZ9)-1),"#,##0")&RIGHT(BZ9,1)

Just looking for custom formatting such as: #,##0? ??

Is this an area not possible for custom formatting in Excel?
XXXXXXXXXXXXXXXXXX

"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*

Hi, thankyou! think some of that is a little past me, but with explanations
I see should be able to work thru it. (I can tear things apart well, and
look like I know more than.. anyways) sorry if not include enough
information, if know what to include, what am doing:

- download Millions / Billions figures, in form of 1.5 or 1.511 M or B for
US Millions/Billions
- mass copy-paste effort (till get rich / hire programmer, nk) is in midst
of other data
- view of 1.5M or 1.5B type data hurts. (right now trying to align
decimals points) with:
- using: format cells, alignment: distrib. distrib. gets rid of view M's
/ B's
- using cond. format for color B / M separately
- other work columns (off this column work well already with ~ LEFT(LEN)-1
stuff

e.g.: (do not need formula's, thanks, what have works), except will look at
prev.
=IF(OR(CE9=0,BZ9={0,"n/a"}),"",(LEFT(BZ9,LEN(BZ9)-1)*IF(RIGHT(BZ9,1)="b",10^9,1)/CE9*$BV$4))

(left(len)-1 * if(B,1000000, else 1 / last price * (fixed cell) 0.3 of
market cap: estimated max avail. shares.

summary: custom format is for view only, not in an equation.

"Sandy Mann" wrote:

nastech,

Assuming that you mean American Billions not British Billions, the Custom
Format that you are searching for is:

[=1000000000]#,,,"B";[=1000000]#,,"M";General

However this will not do what you want. Try this:

Custom Format A1 with the above custom format, then enter 1000000002 You
should see 1B in the cell as you want.
Now in another cell enter the formula:

=RIGHT(A1,1)

Do you see 'B' ? No! you see the figure 2 because the 'B' does not exist,
it is simply formatting the same as if you format a cell as currency you see
the $ or £ sign in front of the numbers but if you test it with =Right(A1,1)
you get the first number not a dollar sign.

I don't really follow what you are trying to do in your formula but try
something like:

=IF(OR(CE138=0,BZ138={0,"n/a"}),"",IF((LEN(INT(BZ138))9)*ISNUMBER(BZ138),BZ1 38,1/CE138*$BV$4))

I assume that if BZ138 is a billion or more then you want to divide BZ138 by
CE138*$BV$4 otherwise return the reciprocal of CE138*$BV$4

If there is no chance that BZ138 will contain decimals then you can replace
the INT(BZ138) with just BZ138

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"nastech" wrote in message
...
what found so far:
Hi, am looking for way to custom format cell to read 1.511B or 1.511M
as: 1B 1M (both B for billion & M.. present in column, thanks)

may need to use worker column to get rid of unwanted digits?
separate work column might not work for me, but would be:
=TEXT(LEFT(BZ9,LEN(BZ9)-1),"#,##0")&RIGHT(BZ9,1)

Just looking for custom formatting such as: #,##0? ??

Is this an area not possible for custom formatting in Excel?
XXXXXXXXXXXXXXXXXX

"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*

nastech,

First off my apologies, the format that I gave you is not what you are
looking for - I had forgotten the in the OP you quoted 1.511 - the format
that I gave you will only show the nearest Billion or Million.

I think that I understand what it is that you are doing better now - if the
result of your formula is:
1,511,000,000 or 1,500,000 or similar
then format the cell as:

[=1000000000]#.###,,,"B";[=1000000]#.###,,"M";General




--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"nastech" wrote in message
...
Hi, thankyou! think some of that is a little past me, but with
explanations
I see should be able to work thru it. (I can tear things apart well, and
look like I know more than.. anyways) sorry if not include enough
information, if know what to include, what am doing:

- download Millions / Billions figures, in form of 1.5 or 1.511 M or B
for
US Millions/Billions
- mass copy-paste effort (till get rich / hire programmer, nk) is in midst
of other data
- view of 1.5M or 1.5B type data hurts. (right now trying to align
decimals points) with:
- using: format cells, alignment: distrib. distrib. gets rid of view
M's
/ B's
- using cond. format for color B / M separately
- other work columns (off this column work well already with ~
LEFT(LEN)-1
stuff

e.g.: (do not need formula's, thanks, what have works), except will look
at
prev.
=IF(OR(CE9=0,BZ9={0,"n/a"}),"",(LEFT(BZ9,LEN(BZ9)-1)*IF(RIGHT(BZ9,1)="b",10^9,1)/CE9*$BV$4))

(left(len)-1 * if(B,1000000, else 1 / last price * (fixed cell) 0.3 of
market cap: estimated max avail. shares.

summary: custom format is for view only, not in an equation.

"Sandy Mann" wrote:

nastech,

Assuming that you mean American Billions not British Billions, the Custom
Format that you are searching for is:

[=1000000000]#,,,"B";[=1000000]#,,"M";General

However this will not do what you want. Try this:

Custom Format A1 with the above custom format, then enter 1000000002 You
should see 1B in the cell as you want.
Now in another cell enter the formula:

=RIGHT(A1,1)

Do you see 'B' ? No! you see the figure 2 because the 'B' does not
exist,
it is simply formatting the same as if you format a cell as currency you
see
the $ or £ sign in front of the numbers but if you test it with
=Right(A1,1)
you get the first number not a dollar sign.

I don't really follow what you are trying to do in your formula but try
something like:

=IF(OR(CE138=0,BZ138={0,"n/a"}),"",IF((LEN(INT(BZ138))9)*ISNUMBER(BZ138),BZ1 38,1/CE138*$BV$4))

I assume that if BZ138 is a billion or more then you want to divide BZ138
by
CE138*$BV$4 otherwise return the reciprocal of CE138*$BV$4

If there is no chance that BZ138 will contain decimals then you can
replace
the INT(BZ138) with just BZ138

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"nastech" wrote in message
...
what found so far:
Hi, am looking for way to custom format cell to read 1.511B or
1.511M
as: 1B 1M (both B for billion & M.. present in column, thanks)

may need to use worker column to get rid of unwanted digits?
separate work column might not work for me, but would be:
=TEXT(LEFT(BZ9,LEN(BZ9)-1),"#,##0")&RIGHT(BZ9,1)

Just looking for custom formatting such as: #,##0? ??

Is this an area not possible for custom formatting in Excel?
XXXXXXXXXXXXXXXXXX

"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*