software

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

Just need quot;Custom Formatquot; 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 0quot;Bquot;

this converts 1.51 to 2 (eg no decimal places) and the B is added on
the end--
robert111
------------------------------------------------------------------------
robert111's Profile: www.excelforum.com/member.php...oamp;userid=31996
View this thread: www.excelforum.com/showthread...hreadid=536678On Wed, 26 Apr 2006 20:18:01 -0700, nastech gt;
wrote:

gt;060426-5 Need Custom format, not a formula
gt;(Not looking for formula's).
gt;
gt;Just need quot;Custom Formatquot; for how column of e.g.: 1.511B will look like: 2B
gt;That which you get by: Right-Click cell, Format Cells.., Number tab,
gt;Custom:
gt;then what do you type in place of #,##0?
gt;(question mark included in this example)
gt;may not be wording this right?
gt;
gt;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

quot;robert111quot; wrote:

gt;
gt; custom format required is 0quot;Bquot;
gt;
gt; this converts 1.51 to 2 (eg no decimal places) and the B is added on
gt; the end
gt;
gt;
gt; --
gt; robert111
gt; ------------------------------------------------------------------------
gt; robert111's Profile: www.excelforum.com/member.php...oamp;userid=31996
gt; View this thread: www.excelforum.com/showthread...hreadid=536678
gt;
gt;

thankyou

quot;Ron Rosenfeldquot; wrote:

gt; On Wed, 26 Apr 2006 20:18:01 -0700, nastech gt;
gt; wrote:
gt;
gt; gt;060426-5 Need Custom format, not a formula
gt; gt;(Not looking for formula's).
gt; gt;
gt; gt;Just need quot;Custom Formatquot; for how column of e.g.: 1.511B will look like: 2B
gt; gt;That which you get by: Right-Click cell, Format Cells.., Number tab,
gt; gt;Custom:
gt; gt;then what do you type in place of #,##0?
gt; gt;(question mark included in this example)
gt; gt;may not be wording this right?
gt; gt;
gt; gt;column has e.g.: 1.511M 1.511B
gt;
gt; Custom formats only work for numeric values. Since 1.511B is not a numeric
gt; value, but rather a text value, you will not be able to do what you want with a
gt; custom format.
gt;
gt;
gt; --ron
gt;

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.)

quot;Ron Rosenfeldquot; wrote:

gt; On Wed, 26 Apr 2006 20:18:01 -0700, nastech gt;
gt; wrote:
gt;
gt; gt;060426-5 Need Custom format, not a formula
gt; gt;(Not looking for formula's).
gt; gt;
gt; gt;Just need quot;Custom Formatquot; for how column of e.g.: 1.511B will look like: 2B
gt; gt;That which you get by: Right-Click cell, Format Cells.., Number tab,
gt; gt;Custom:
gt; gt;then what do you type in place of #,##0?
gt; gt;(question mark included in this example)
gt; gt;may not be wording this right?
gt; gt;
gt; gt;column has e.g.: 1.511M 1.511B
gt;
gt; Custom formats only work for numeric values. Since 1.511B is not a numeric
gt; value, but rather a text value, you will not be able to do what you want with a
gt; custom format.
gt;
gt;
gt; --ron
gt;


Sorry I didnt appreciate 1.51B was a text string

say it is in cell A1

len(A1) returns 5
search(A1,quot;.quot;) 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 quot;the 1 returned in the LEFT
function otherwise add 0 to the 1 returned in the left functionquot;

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: www.excelforum.com/member.php...oamp;userid=31996
View this thread: www.excelforum.com/showthread...hreadid=536678060427 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 amp; assume in Millions, unless has a quot;Bquot;
CAN i DO SOMETHING WITH THAT? Thanks.quot;robert111quot; wrote:

gt;
gt; Sorry I didnt appreciate 1.51B was a text string
gt;
gt; say it is in cell A1
gt;
gt; len(A1) returns 5
gt; search(A1,quot;.quot;) returns 2
gt; 5-2=3
gt; 3-2=1
gt; left(a1,1)=1
gt; 2 1=3
gt; mid(A1,3,(5-2-1)) returns 51
gt; len(51)=2
gt; 10 to the power 2 returns 100
gt; 100/2 returns 50
gt; if 51 is bigger than 50 then add 1 to quot;the 1 returned in the LEFT
gt; function otherwise add 0 to the 1 returned in the left functionquot;
gt;
gt; you now have your desired answer of 2 (it may be text so you need to
gt; use the value function to turn it into a number)
gt;
gt; FINALLY format it as in my previous text.
gt;
gt;
gt; --
gt; robert111
gt; ------------------------------------------------------------------------
gt; robert111's Profile: www.excelforum.com/member.php...oamp;userid=31996
gt; View this thread: www.excelforum.com/showthread...hreadid=536678
gt;
gt;

currently working: format cells, alignment (distributed, distributed) gets
rid of view of M amp; B, Looking to line up decimal point if quot;can-doquot;. thanks

quot;robert111quot; wrote:

gt;
gt; Sorry I didnt appreciate 1.51B was a text string
gt;
gt; say it is in cell A1
gt;
gt; len(A1) returns 5
gt; search(A1,quot;.quot;) returns 2
gt; 5-2=3
gt; 3-2=1
gt; left(a1,1)=1
gt; 2 1=3
gt; mid(A1,3,(5-2-1)) returns 51
gt; len(51)=2
gt; 10 to the power 2 returns 100
gt; 100/2 returns 50
gt; if 51 is bigger than 50 then add 1 to quot;the 1 returned in the LEFT
gt; function otherwise add 0 to the 1 returned in the left functionquot;
gt;
gt; you now have your desired answer of 2 (it may be text so you need to
gt; use the value function to turn it into a number)
gt;
gt; FINALLY format it as in my previous text.
gt;
gt;
gt; --
gt; robert111
gt; ------------------------------------------------------------------------
gt; robert111's Profile: www.excelforum.com/member.php...oamp;userid=31996
gt; View this thread: www.excelforum.com/showthread...hreadid=536678
gt;
gt;

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

gt;thankyou again, is there any work around? maybe not critical then, but should
gt;I post as a script request question, or do you think that might be wasted
gt;time? (resources etc.)
gt;
gt;quot;Ron Rosenfeldquot; wrote:
gt;
gt;gt; On Wed, 26 Apr 2006 20:18:01 -0700, nastech gt;
gt;gt; wrote:
gt;gt;
gt;gt; gt;060426-5 Need Custom format, not a formula
gt;gt; gt;(Not looking for formula's).
gt;gt; gt;
gt;gt; gt;Just need quot;Custom Formatquot; for how column of e.g.: 1.511B will look like: 2B
gt;gt; gt;That which you get by: Right-Click cell, Format Cells.., Number tab,
gt;gt; gt;Custom:
gt;gt; gt;then what do you type in place of #,##0?
gt;gt; gt;(question mark included in this example)
gt;gt; gt;may not be wording this right?
gt;gt; gt;
gt;gt; gt;column has e.g.: 1.511M 1.511B
gt;gt;
gt;gt; Custom formats only work for numeric values. Since 1.511B is not a numeric
gt;gt; value, but rather a text value, you will not be able to do what you want with a
gt;gt; custom format.
gt;gt;
gt;gt;
gt;gt; --ron
gt;gt;

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 quot;text stringquot; or a quot;real
numberquot;, the method would be different.

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