Hi, I guesse have to believe cannot format text string must be true.
Suprised sometimes to see the work-arounds found. On other hand, cannot
believe MS does not allow formatting as such.

- do not believe there are any tags, command to receive data is: (not
important?)
http://quote.yahoo.com/d/quotes.csv?...kj1va2ghl1pld1

- swtiches get: yr lo/hi, mcap (market capital in M/B), vol, 3m vol, day
lo/hi, LAST, prev close, time, date

- the exact data I get (although ea number in separate cell) is:
(needing to view it as: 810M 11B 115B 3B 60B)

810.1M 11.359B 114.7B 3.180B 60.489B 4.017B 890.4M 355.8B 183.7B 129.2B
32.538B 1.646B 1.780B 11.934B 10.949B 280.0B

- for your effort, have some formula's for you if interested in that kind of
thing:
Percent Change: (to-fm)/fm% -or- to-fm%-100
Net Gain: (in-fee)/fm*to-in
Relative Positon: (last-fm)/(to-fm)*100 -or- (to-fm)*in%+fm (not in any
book)
Momentum: concept you wouldn't believe, have other stuff can't list.
If interested in swapping some ideas / help, send to nasgentech at yahoo
thanks.

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

"Sandy Mann" wrote:

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
...
need to see incomming: 1.5B or 1.511B look like 2B
and 1.5M or 1.511M look like 2M thanks

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