Need Custom format, not a formula

[Guest]

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

 

[robert111]

custom format required is 0"B"

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

 

[Ron Rosenfeld]

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

 

[Guest]

thankyou

 

[Guest]

thankyou

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

 

[Guest]

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

 

[robert111]

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.

 

[Guest]

060427 Re: 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.

 

[Guest]

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

 

[Ron Rosenfeld]

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

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

 

[Guest]

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

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

Hi ! lost you lost too? hey, if that was an answer you gave before sorry.
(I am hi contrast, look like a rocket-scientist one minute... just put alot
of effort in sometimes looks good..)

there you are: what do I do with that? will just try to past the whole
thing in the custom cell box & see what happens... min..
(shoud I/ could I use power to 9/6 ? 10^9, 10^6

pasted exactly what you have, including: general = no change/ don't see e.g.:
1B 1M tried:

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

no change, do I have some other setting that might interfere? Thanks !

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

The format I gave was intended for the cell containing your formula not the
incoming data. If your imcoming data is text then as far as I know there is
no formatting that can change the actual text of the cell.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

Hi ! lost you lost too? hey, if that was an answer you gave before
sorry.
(I am hi contrast, look like a rocket-scientist one minute... just put
alot
of effort in sometimes looks good..)

there you are: what do I do with that? will just try to past the whole
thing in the custom cell box & see what happens... min..
(shoud I/ could I use power to 9/6 ? 10^9, 10^6

pasted exactly what you have, including: general = no change/ don't see
e.g.:
1B 1M tried:

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

no change, do I have some other setting that might interfere? Thanks !

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Ron Rosenfeld]

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

 

[Guest]

Hi, thanks for reply, had been working with user: sandy mann in different
window, not sure if closer to what want. He? thought I was abbreviating
(always trying to be literal). so he though I wanted to convert billion
figure: 1,511,000,000 to ? (I need 2B) with his example (last word, he
hasn't gotten back since posted my idea below that):

his: (my numbers are in 1.5B or M format, not 1000000000 etc)
[>=1000000000]#.###,,,"B";[>=1000000]#.###,,"M";General

gross idea: (probably won't work, general means formatting?)
[#.#B]#,,,"B";[#.#M]#,,"M";General


in the following, have to leave column in place, leave numbers as is
(although not supposed to, are some less than 1M, with straight up numbers).
I need (as below): 810M 11B 115B 3B 60B 4B 890M....

actual figures are as stated. I did a direct copy-paste, here (with my tool):

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 4.972B 1.276B 1.066B 33.017B
75.059B 23.865B 67.961B 127.5B 7.5M 7.5M 36.9M 9.8M 22.0M 70.7M
93.7M 91.0M 3.3M 2.1M 18.5M 7.3M 11.0M 11.2M 11.3M 3.0M 5.4M 39.9M 14.0M
11.4M 177.9M 27.1M 105.9M 14.3M 46.2M 188.4M 32.4M 172.7M 33.3M 318.1M 178.9M
131.2M 4.508B 1.9M 34.5M 22.991B 126.6B 1.402B 3.071B 3.091B
43.523B 3.080B 1.962B 23.713B bta

 

[Ron Rosenfeld]

in the following, have to leave column in place, leave numbers as is
(although not supposed to, are some less than 1M, with straight up numbers).
I need (as below): 810M 11B 115B 3B 60B 4B 890M....

actual figures are as stated. I did a direct copy-paste, here (with my tool):

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 4.972B 1.276B 1.066B 33.017B
75.059B 23.865B 67.961B 127.5B 7.5M 7.5M 36.9M 9.8M 22.0M 70.7M
93.7M 91.0M 3.3M 2.1M 18.5M 7.3M 11.0M 11.2M 11.3M 3.0M 5.4M 39.9M 14.0M
11.4M 177.9M 27.1M 105.9M 14.3M 46.2M 188.4M 32.4M 172.7M 33.3M 318.1M 178.9M
131.2M 4.508B 1.9M 34.5M 22.991B 126.6B 1.402B 3.071B 3.091B
43.523B 3.080B 1.962B 23.713B bta

Are all those values in a single cell? Or is there one entry per cell?

I do note that in your example, every "number" is followed by an M or a B.
Will that always be the case?

Assume:

1. One entry per cell.
2. Each entry always ends with a letter.

Formula:
=ROUND(LEFT(A1,LEN(A1)-1),0)&RIGHT(A1,1)

If your data is different, please state exactly how it differs.


--ron

 

[Guest]

hi... have equations to manipulate the data at will.
- each number is 99% of time followed by B or M, occasionally will have
straight up number less than 1M, e.g.: 832555
- each number is in its own cell.

What is need is Custom Format for "View" of those numbers, as previous
couple of posts, maybe something like:

WHAT NEED:
gross idea: (probably won't work, general means formatting?) FIX NEXT LINE.
[#.#B]#,,,"B";[#.#M]#,,"M";General

From SandyMann: (my numbers are in 1.5B or M format, "NOT" 1000000000 etc)
[>=1000000000]#.###,,,"B";[>=1000000]#.###,,"M";General

XXXXXXXXXXXXXXXXXXXXXXXX

 

[Ron Rosenfeld]

hi... have equations to manipulate the data at will.
- each number is 99% of time followed by B or M, occasionally will have
straight up number less than 1M, e.g.: 832555
- each number is in its own cell.

How do you want a value like 83255 to be displayed in the cell?

What is need is Custom Format for "View" of those numbers, as previous
couple of posts, maybe something like:

WHAT NEED:
gross idea: (probably won't work, general means formatting?) FIX NEXT LINE.
[#.#B]#,,,"B";[#.#M]#,,"M";General

As has been written a number of times by various contributors, formatting will
only affect numeric values. There is no way to have a cell contain a value of
1.5B and display 2B. You can do that in an adjacent cell with a formula, or
you can use a macro to actually change the value in the same cell, but then you
will lose your original value.

So you are going to have to decide what you want now that you know what is
possible.

Once you answer my question as to how you want 832555 to be displayed, then I
can modify the formula I gave you earlier to take that into account.
--ron

 

[Guest]

Hi, thanks for the patience. I guesse I've had a couple of precepts, but
wasn't sure what real answer was..

- as presented, have these that work:
=TEXT(LEFT(BZ9,LEN(BZ9)-1),"#,##0")&RIGHT(BZ9,1)

=IF(OR(CE9=0,BZ9={0,"n/a"}),"",
(LEFT(BZ9,LEN(BZ9)-1)*IF(RIGHT(BZ9,1)="b",10^9,1)/CE9*$BV$4))

- also figured would be a work around for just about anything/ can't beleive
this isn't possible for so simple a thing, would be?: #? as a format
- don't think its a math thing? just lack of programming
- told by more than one, can program a computer to do anything.. emc mass
data storage programmers... thanks again

hi... have equations to manipulate the data at will.
- each number is 99% of time followed by B or M, occasionally will have
straight up number less than 1M, e.g.: 832555
- each number is in its own cell.

How do you want a value like 83255 to be displayed in the cell?

What is need is Custom Format for "View" of those numbers, as previous
couple of posts, maybe something like:

WHAT NEED:
gross idea: (probably won't work, general means formatting?) FIX NEXT LINE.
[#.#B]#,,,"B";[#.#M]#,,"M";General

As has been written a number of times by various contributors, formatting will
only affect numeric values. There is no way to have a cell contain a value of
1.5B and display 2B. You can do that in an adjacent cell with a formula, or
you can use a macro to actually change the value in the same cell, but then you
will lose your original value.

So you are going to have to decide what you want now that you know what is
possible.

Once you answer my question as to how you want 832555 to be displayed, then I
can modify the formula I gave you earlier to take that into account.
--ron