formula help

[Jamie]

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!!

 

[Teethless mama]

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

 

[Aivis]

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

 

[Jamie]

Awesome thanks!!

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

 

[Jamie]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Jamie]

You are wonderful

Perhaps this...

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

Rick

 

[David Biddulph]

Perhaps this...

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

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

 

[Rick Rothstein \(MVP - VB\)]

Got another one for ya...

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

 

[Harlan Grove]

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C31,"eq",""),"up",""),
"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"},""))

 

[Harlan Grove]

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