Custom Format Cell

[nastech]

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*

[Sandy Mann]

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

[email protected]
[email protected] with @tiscali.co.uk


"nastech" <[email protected]> wrote in message
news:[email protected]...
> 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]

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
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "nastech" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "nastech" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "nastech" <[email protected]> wrote in message
> news:[email protected]...
> > 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*
> >
>
>
>
>

[Sandy Mann]

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),BZ138,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

[email protected]
[email protected] with @tiscali.co.uk


"nastech" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "nastech" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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]

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),BZ138,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
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "nastech" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >>
> >> [email protected]
> >> [email protected] with @tiscali.co.uk
> >>
> >>
> >> "nastech" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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*
> >> >
> >>
> >>
> >>
> >>
>
>
>

[Sandy Mann]

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

[email protected]
[email protected] with @tiscali.co.uk


"nastech" <[email protected]> wrote in message
news:[email protected]...
> 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),BZ138,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
>>
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "nastech" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >>
>> >> [email protected]
>> >> [email protected] with @tiscali.co.uk
>> >>
>> >>
>> >> "nastech" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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]

Negative, my problem for not iterating that what am typing is exact.

Here we are: (mega-detailed when can be) the following is what is "exactly
downloaded"

1.5B
1.5M
1.511M
200.0B

that is all combinations, my equation posted is hard to look at, but would
have be clue (hard because what does not happen is left out of equation,
hence shorter equation, whole nother discussion) format working with is as
above. 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
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "nastech" <[email protected]> wrote in message
> news:[email protected]...
> > 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),BZ138,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
> >>
> >> [email protected]
> >> [email protected] with @tiscali.co.uk
> >>
> >>
> >> "nastech" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >>
> >> >> [email protected]
> >> >> [email protected] with @tiscali.co.uk
> >> >>
> >> >>
> >> >> "nastech" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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]

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
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "nastech" <[email protected]> wrote in message
> news:[email protected]...
> > 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),BZ138,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
> >>
> >> [email protected]
> >> [email protected] with @tiscali.co.uk
> >>
> >>
> >> "nastech" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >>
> >> >> [email protected]
> >> >> [email protected] with @tiscali.co.uk
> >> >>
> >> >>
> >> >> "nastech" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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]

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
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "nastech" <[email protected]> wrote in message
> news:[email protected]...
> > 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),BZ138,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
> >>
> >> [email protected]
> >> [email protected] with @tiscali.co.uk
> >>
> >>
> >> "nastech" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >>
> >> >> [email protected]
> >> >> [email protected] with @tiscali.co.uk
> >> >>
> >> >>
> >> >> "nastech" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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*
> >> >> >
> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
>

[Sandy Mann]

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

[email protected]
[email protected] with @tiscali.co.uk


"nastech" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "nastech" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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),BZ138,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
>> >>
>> >> [email protected]
>> >> [email protected] with @tiscali.co.uk
>> >>
>> >>
>> >> "nastech" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >> >>
>> >> >> [email protected]
>> >> >> [email protected] with @tiscali.co.uk
>> >> >>
>> >> >>
>> >> >> "nastech" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > 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*
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
>>

[Sandy Mann]

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

[email protected]
[email protected] with @tiscali.co.uk


"nastech" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "nastech" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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),BZ138,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
>> >>
>> >> [email protected]
>> >> [email protected] with @tiscali.co.uk
>> >>
>> >>
>> >> "nastech" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >> >>
>> >> >> [email protected]
>> >> >> [email protected] with @tiscali.co.uk
>> >> >>
>> >> >>
>> >> >> "nastech" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > 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]

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
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "nastech" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >>
> >> [email protected]
> >> [email protected] with @tiscali.co.uk
> >>
> >>
> >> "nastech" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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),BZ138,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
> >> >>
> >> >> [email protected]
> >> >> [email protected] with @tiscali.co.uk
> >> >>
> >> >>
> >> >> "nastech" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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
> >> >> >>
> >> >> >> [email protected]
> >> >> >> [email protected] with @tiscali.co.uk
> >> >> >>
> >> >> >>
> >> >> >> "nastech" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >> > 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*
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
> >>
>
>
>