Current Cell is C31: 1.3794eq
Desired result: C150: 1.3794

The problem is that C31 will vary from day to day. It will always be
numbers followed by text (either "eq", "up", or "down"). The number will not
always show the same number of places. Tomorrow it may be C31: 1.48down
I need a formula that will not have to be changed on a daily basis.
Please help!!

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C31,"eq",""),"up ",""),"down","")+0


"Jamie" wrote:

Current Cell is C31: 1.3794eq
Desired result: C150: 1.3794

The problem is that C31 will vary from day to day. It will always be
numbers followed by text (either "eq", "up", or "down"). The number will not
always show the same number of places. Tomorrow it may be C31: 1.48down
I need a formula that will not have to be changed on a daily basis.
Please help!!

How about this?:

=IF(OR(RIGHT(B2;2)="eq";RIGHT(B2;2)="up");LEFT(B2; LEN(B2)-2)*1;LEFT(B2;LEN(B2)-4)*1)

B C
2 1,3794eq 1,3794
3 2,560068up 2,560068
4 1,458748down 1,458748


--
A.B.


"Jamie" rakstîja:

Current Cell is C31: 1.3794eq
Desired result: C150: 1.3794

The problem is that C31 will vary from day to day. It will always be
numbers followed by text (either "eq", "up", or "down"). The number will not
always show the same number of places. Tomorrow it may be C31: 1.48down
I need a formula that will not have to be changed on a daily basis.
Please help!!

Awesome thanks!!

"Teethless mama" wrote:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C31,"eq",""),"up ",""),"down","")+0


"Jamie" wrote:

Current Cell is C31: 1.3794eq
Desired result: C150: 1.3794

The problem is that C31 will vary from day to day. It will always be
numbers followed by text (either "eq", "up", or "down"). The number will not
always show the same number of places. Tomorrow it may be C31: 1.48down
I need a formula that will not have to be changed on a daily basis.
Please help!!

Got another one for ya...
D22: 1,305,393
I need E4: 1,305. (need that decimal and the comma)

I am rounding to the nearest thousand, and then abbreviating it.
Like my other question, D22 will vary from day to day and will not always
have the same number of characters. Tomorrow it could be D22: 787,409 and I
would need 787.

"Teethless mama" wrote:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C31,"eq",""),"up ",""),"down","")+0


"Jamie" wrote:

Current Cell is C31: 1.3794eq
Desired result: C150: 1.3794

The problem is that C31 will vary from day to day. It will always be
numbers followed by text (either "eq", "up", or "down"). The number will not
always show the same number of places. Tomorrow it may be C31: 1.48down
I need a formula that will not have to be changed on a daily basis.
Please help!!

Got another one for ya...
D22: 1,305,393
I need E4: 1,305. (need that decimal and the comma)

I am rounding to the nearest thousand, and then abbreviating it.
Like my other question, D22 will vary from day to day and will not always
have the same number of characters. Tomorrow it could be D22: 787,409
and I
would need 787.

Perhaps this...

E4: =TEXT(INT(D22/1000),"#,###.")

Rick

You are wonderful :)

"Rick Rothstein (MVP - VB)" wrote:

Got another one for ya...
D22: 1,305,393
I need E4: 1,305. (need that decimal and the comma)

I am rounding to the nearest thousand, and then abbreviating it.
Like my other question, D22 will vary from day to day and will not always
have the same number of characters. Tomorrow it could be D22: 787,409
and I
would need 787.

Perhaps this...

E4: =TEXT(INT(D22/1000),"#,###.")

Rick

"Rick Rothstein (MVP - VB)" wrote in
message ...
Got another one for ya...
D22: 1,305,393
I need E4: 1,305. (need that decimal and the comma)

I am rounding to the nearest thousand, and then abbreviating it.
Like my other question, D22 will vary from day to day and will not always
have the same number of characters. Tomorrow it could be D22: 787,409
and I
would need 787.

Perhaps this...

E4: =TEXT(INT(D22/1000),"#,###.")

He said he wanted rounding to the *nearest* thousand, so wouldn't
=TEXT(D22/1000,"#,###.") be better?
--
David Biddulph

Got another one for ya...
D22: 1,305,393
I need E4: 1,305. (need that decimal and the comma)

I am rounding to the nearest thousand, and then abbreviating it.
Like my other question, D22 will vary from day to day and will not
always
have the same number of characters. Tomorrow it could be D22: 787,409
and I
would need 787.

Perhaps this...

E4: =TEXT(INT(D22/1000),"#,###.")

He said he wanted rounding to the *nearest* thousand, so wouldn't
=TEXT(D22/1000,"#,###.") be better?

Damn! You are probably right... the word "nearest" simply didn't register
when I first read it. Thanks for picking up on that.

Rick

Got another one for ya...
D22: 1,305,393
I need E4: 1,305. (need that decimal and the comma)

I am rounding to the nearest thousand, and then abbreviating it.
Like my other question, D22 will vary from day to day and will not
always
have the same number of characters. Tomorrow it could be D22: 787,409
and I
would need 787.

Perhaps this...

E4: =TEXT(INT(D22/1000),"#,###.")

He said he wanted rounding to the *nearest* thousand, so wouldn't
=TEXT(D22/1000,"#,###.") be better?

Damn! You are probably right... the word "nearest" simply didn't register
when I first read it. Thanks for picking up on that.

Rick

Teethless mama wrote...
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C31,"eq",""),"u p",""),
"down","")+0
....

FTHOI, shorter work-alikes.

=--LEFT(x,MIN(SEARCH({"up","down","eq"},x&"updowneq") )-1)

or

=LOOKUP(1E+307,--SUBSTITUTE(x,{"up","down","eq"},""))

Teethless mama wrote...
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C31,"eq",""),"u p",""),
"down","")+0
....

FTHOI, shorter work-alikes.

=--LEFT(x,MIN(SEARCH({"up","down","eq"},x&"updowneq") )-1)

or

=LOOKUP(1E+307,--SUBSTITUTE(x,{"up","down","eq"},""))

"David Biddulph" <groups [at] biddulph.org.uk wrote...
....
He said he wanted rounding to the *nearest* thousand, so wouldn't
=TEXT(D22/1000,"#,###.") be better?

Maybe, but why bother with the division?

=TEXT(D22,"#,##0,\.")

Note: "#,###,\." format would display x < 500 as "." while "#,##0,\."
format would display such x as "0."

"David Biddulph" <groups [at] biddulph.org.uk wrote...
....
He said he wanted rounding to the *nearest* thousand, so wouldn't
=TEXT(D22/1000,"#,###.") be better?

Maybe, but why bother with the division?

=TEXT(D22,"#,##0,\.")

Note: "#,###,\." format would display x < 500 as "." while "#,##0,\."
format would display such x as "0."