#1   Report Post  
Posted to microsoft.public.excel.misc
nastech
 
Posts: n/a
Default Custom Format Cell

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*

  #2   Report Post  
Posted to microsoft.public.excel.misc
Sandy Mann
 
Posts: n/a
Default Custom Format Cell

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*




  #3   Report Post  
Posted to microsoft.public.excel.misc
nastech
 
Posts: n/a
Default Custom Format Cell

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*





  #4   Report Post  
Posted to microsoft.public.excel.misc
nastech
 
Posts: n/a
Default Custom Format Cell

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*





  #5   Report Post  
Posted to microsoft.public.excel.misc
nastech
 
Posts: n/a
Default Custom Format Cell

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*







  #6   Report Post  
Posted to microsoft.public.excel.misc
Sandy Mann
 
Posts: n/a
Default Custom Format Cell

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*







  #7   Report Post  
Posted to microsoft.public.excel.misc
nastech
 
Posts: n/a
Default Custom Format Cell

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*








  #8   Report Post  
Posted to microsoft.public.excel.misc
Sandy Mann
 
Posts: n/a
Default Custom Format Cell

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*











  #9   Report Post  
Posted to microsoft.public.excel.misc
nastech
 
Posts: n/a
Default Custom Format Cell

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

Here we a (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


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*












  #10   Report Post  
Posted to microsoft.public.excel.misc
nastech
 
Posts: n/a
Default Custom Format Cell

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*














  #11   Report Post  
Posted to microsoft.public.excel.misc
nastech
 
Posts: n/a
Default Custom Format Cell

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*












  #12   Report Post  
Posted to microsoft.public.excel.misc
Sandy Mann
 
Posts: n/a
Default Custom Format Cell

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*














  #13   Report Post  
Posted to microsoft.public.excel.misc
Sandy Mann
 
Posts: n/a
Default Custom Format Cell

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*














  #14   Report Post  
Posted to microsoft.public.excel.misc
nastech
 
Posts: n/a
Default Custom Format Cell

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*















Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
custom format cell, if value is 0, then write "-" Toto Sanderson Excel Discussion (Misc queries) 4 February 24th 06 04:06 PM
Urgent date/scheduling calc needed jct Excel Worksheet Functions 3 February 24th 06 02:36 AM
Excel - cell format switches to custom when switching language louisp Excel Discussion (Misc queries) 0 February 10th 06 07:23 PM
Why does Custom Format not control Cell Display? Dennis Excel Discussion (Misc queries) 1 June 11th 05 12:28 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 12:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"