think ideas (albeit wrong) would be like:

#? for number with any single character

the following I see not in use as format commin in is exactly: 1.5B or 1.5M
for exact variable listed: =1000000000 all I have is 1.5 with a B

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

something like (wrong i know)
[#.#"B"]#"B";[#.#"M"]#"M";General

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
"Sandy Mann" wrote:

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*

I replied last night but my reply seems to have disappeared into cyber
space. The same thing happened to me the other day so it is obviously a
conspiricy . <g

Repost of previous post:

Well, if your incoming data is like 1.5B or 1.511B then that will be text
not a number so you cannot, as far as I know, format it to look like 2B.

An additional problem may be that if you are downloading this data from the
Web then, from what I hear, it will probably have non-breaking character
CHAR(16) tagged on to the end or perhaps even the start. This would be a
problem when you try to test for a B at the end of the data. If the data
say in A1 looks like 1.5B (or something similar), what return do you get
from =LEN(A1) Do you get 4 or do you get 5 or even 6?


--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"nastech" wrote in message
...
think ideas (albeit wrong) would be like:

#? for number with any single character

the following I see not in use as format commin in is exactly: 1.5B or
1.5M
for exact variable listed: =1000000000 all I have is 1.5 with a B

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

something like (wrong i know)
[#.#"B"]#"B";[#.#"M"]#"M";General

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
"Sandy Mann" wrote:

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*