060426-5 Need Custom format, not a formula
(Not looking for formula's).

Just need "Custom Format" for how column of e.g.: 1.511B will look like: 2B
That which you get by: Right-Click cell, Format Cells.., Number tab,
Custom:
then what do you type in place of #,##0?
(question mark included in this example)
may not be wording this right?

column has e.g.: 1.511M 1.511B

custom format required is 0"B"

this converts 1.51 to 2 (eg no decimal places) and the B is added on
the end


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=536678

thankyou

"robert111" wrote:


custom format required is 0"B"

this converts 1.51 to 2 (eg no decimal places) and the B is added on
the end


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=536678

On Wed, 26 Apr 2006 20:18:01 -0700, nastech
wrote:

060426-5 Need Custom format, not a formula
(Not looking for formula's).

Just need "Custom Format" for how column of e.g.: 1.511B will look like: 2B
That which you get by: Right-Click cell, Format Cells.., Number tab,
Custom:
then what do you type in place of #,##0?
(question mark included in this example)
may not be wording this right?

column has e.g.: 1.511M 1.511B

Custom formats only work for numeric values. Since 1.511B is not a numeric
value, but rather a text value, you will not be able to do what you want with a
custom format.


--ron

thankyou

"Ron Rosenfeld" wrote:

On Wed, 26 Apr 2006 20:18:01 -0700, nastech
wrote:

060426-5 Need Custom format, not a formula
(Not looking for formula's).

Just need "Custom Format" for how column of e.g.: 1.511B will look like: 2B
That which you get by: Right-Click cell, Format Cells.., Number tab,
Custom:
then what do you type in place of #,##0?
(question mark included in this example)
may not be wording this right?

column has e.g.: 1.511M 1.511B

Custom formats only work for numeric values. Since 1.511B is not a numeric
value, but rather a text value, you will not be able to do what you want with a
custom format.


--ron

thankyou again, is there any work around? maybe not critical then, but should
I post as a script request question, or do you think that might be wasted
time? (resources etc.)

"Ron Rosenfeld" wrote:

On Wed, 26 Apr 2006 20:18:01 -0700, nastech
wrote:

060426-5 Need Custom format, not a formula
(Not looking for formula's).

Just need "Custom Format" for how column of e.g.: 1.511B will look like: 2B
That which you get by: Right-Click cell, Format Cells.., Number tab,
Custom:
then what do you type in place of #,##0?
(question mark included in this example)
may not be wording this right?

column has e.g.: 1.511M 1.511B

Custom formats only work for numeric values. Since 1.511B is not a numeric
value, but rather a text value, you will not be able to do what you want with a
custom format.


--ron

Sorry I didnt appreciate 1.51B was a text string

say it is in cell A1

len(A1) returns 5
search(A1,".") returns 2
5-2=3
3-2=1
left(a1,1)=1
2+1=3
mid(A1,3,(5-2-1)) returns 51
len(51)=2
10 to the power 2 returns 100
100/2 returns 50
if 51 is bigger than 50 then add 1 to "the 1 returned in the LEFT
function otherwise add 0 to the 1 returned in the left function"

you now have your desired answer of 2 (it may be text so you need to
use the value function to turn it into a number)

FINALLY format it as in my previous text.


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=536678

060427 Need Custom format, not a formula
Hi, you beat me back. thought I might have something of a work around...
have to say I can rip things apart, put back together.. maybe not that good
though.
can you give some context what I can do with those items?

and was thinking of items together for workaround:
a. make ms fix it :)
b. - use conditional format color for all over 1000 to denote Billions
(other work columns that work this column e.g.: work with ~ left(len)-1
stuff
- left justify (have to re-locate lining up decimal formatting, if
possible here?

again, with your stuff, is that for use in formula's? will keep / review,
but download of data I get is in form of mostly 1.5M or 1.5B some
1.511...

c. would use: delete all M's & assume in Millions, unless has a "B"
CAN i DO SOMETHING WITH THAT? Thanks.


"robert111" wrote:


Sorry I didnt appreciate 1.51B was a text string

say it is in cell A1

len(A1) returns 5
search(A1,".") returns 2
5-2=3
3-2=1
left(a1,1)=1
2+1=3
mid(A1,3,(5-2-1)) returns 51
len(51)=2
10 to the power 2 returns 100
100/2 returns 50
if 51 is bigger than 50 then add 1 to "the 1 returned in the LEFT
function otherwise add 0 to the 1 returned in the left function"

you now have your desired answer of 2 (it may be text so you need to
use the value function to turn it into a number)

FINALLY format it as in my previous text.


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=536678

currently working: format cells, alignment (distributed, distributed) gets
rid of view of M & B, Looking to line up decimal point if "can-do". thanks

"robert111" wrote:


Sorry I didnt appreciate 1.51B was a text string

say it is in cell A1

len(A1) returns 5
search(A1,".") returns 2
5-2=3
3-2=1
left(a1,1)=1
2+1=3
mid(A1,3,(5-2-1)) returns 51
len(51)=2
10 to the power 2 returns 100
100/2 returns 50
if 51 is bigger than 50 then add 1 to "the 1 returned in the LEFT
function otherwise add 0 to the 1 returned in the left function"

you now have your desired answer of 2 (it may be text so you need to
use the value function to turn it into a number)

FINALLY format it as in my previous text.


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=536678

On Thu, 27 Apr 2006 08:40:02 -0700, nastech
wrote:

thankyou again, is there any work around? maybe not critical then, but should
I post as a script request question, or do you think that might be wasted
time? (resources etc.)

"Ron Rosenfeld" wrote:

On Wed, 26 Apr 2006 20:18:01 -0700, nastech
wrote:

060426-5 Need Custom format, not a formula
(Not looking for formula's).

Just need "Custom Format" for how column of e.g.: 1.511B will look like: 2B
That which you get by: Right-Click cell, Format Cells.., Number tab,
Custom:
then what do you type in place of #,##0?
(question mark included in this example)
may not be wording this right?

column has e.g.: 1.511M 1.511B

Custom formats only work for numeric values. Since 1.511B is not a numeric
value, but rather a text value, you will not be able to do what you want with a
custom format.


--ron


If you can use a formula, it would be simple to translate your 1.511B; 1.511M;
etc.

Without knowing the range of values you might be using, it's a bit hard to come
up with a general formula.

Also, depending on whether you want the result to be a "text string" or a "real
number", the method would be different.

So if you can define your requirements more completely, a solution will be
forthcoming.
--ron

Hi, sorry, can be difficult to explain, kept getting formula's - needed
format.. but had to repost 4-6 times... losing track:

downloading data to column where I mostly need it (midst other down loaded
data, by copy-paste at this point: thats the rub/why)

don't have use for separate work column for that. other columns that work
off that data, e.g.: 1.5B some 1.511B in length, 1.5M.... have equations
that work fine using concept of: Left(LEN)-1 etc.

column data in very hard to read / too wide. (as above:
120.511B
1.0M
1.2B ...

think uderstand from someone here you can not format text string.
was using what can for work around, maybe looking for a center? justify
decimal point where left/right move out from center (read / saw that
yesterday morning)
for life of MS not searcable: center-justify on decimal point...?? saw it,
know its there, experimenting with:

- if haven't said, would have MS allow number/ text string format e.g.:
#.0? (? - single char), especially since that is how data is being sent,
proves the need.
- alignment, distributed, gets rid of viewing M B
- cond. format so 1B goes different color (M/B still there) other eq's work
- idea: can delete all M's, but danger, some figures will be <1M... and
adjust other formulas accordingly (but might do since minimum standard is set
at 1M..

can't think of anything else right now, just flying around left-right
justify, distributed, trying to find: center on decimal?..




"Ron Rosenfeld" wrote:

On Thu, 27 Apr 2006 08:40:02 -0700, nastech
wrote:

thankyou again, is there any work around? maybe not critical then, but should
I post as a script request question, or do you think that might be wasted
time? (resources etc.)

"Ron Rosenfeld" wrote:

On Wed, 26 Apr 2006 20:18:01 -0700, nastech
wrote:

060426-5 Need Custom format, not a formula
(Not looking for formula's).

Just need "Custom Format" for how column of e.g.: 1.511B will look like: 2B
That which you get by: Right-Click cell, Format Cells.., Number tab,
Custom:
then what do you type in place of #,##0?
(question mark included in this example)
may not be wording this right?

column has e.g.: 1.511M 1.511B

Custom formats only work for numeric values. Since 1.511B is not a numeric
value, but rather a text value, you will not be able to do what you want with a
custom format.


--ron


If you can use a formula, it would be simple to translate your 1.511B; 1.511M;
etc.

Without knowing the range of values you might be using, it's a bit hard to come
up with a general formula.

Also, depending on whether you want the result to be a "text string" or a "real
number", the method would be different.

So if you can define your requirements more completely, a solution will be
forthcoming.
--ron

"nastech" wrote in message
...
Hi, sorry, can be difficult to explain, kept getting formula's - needed
format.. but had to repost 4-6 times... losing track:


Your're not the only one <g

If it is still a format you are looking for then I posted one in one of your
other threads:


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


--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk

On Thu, 27 Apr 2006 13:25:01 -0700, nastech
wrote:

Hi, sorry, can be difficult to explain, kept getting formula's - needed
format.. but had to repost 4-6 times... losing track:

downloading data to column where I mostly need it (midst other down loaded
data, by copy-paste at this point: thats the rub/why)

don't have use for separate work column for that. other columns that work
off that data, e.g.: 1.5B some 1.511B in length, 1.5M.... have equations
that work fine using concept of: Left(LEN)-1 etc.

column data in very hard to read / too wide. (as above:
120.511B
1.0M
1.2B ...


Let me ask some questions which can be answered simply, in order to try to
clarify the situation.

1. Does every entry end with a letter?
2. Are the letters always "B" or "M"?

3. If there are other letters that an entry might end with, what are they and
what do they stand for?


--ron