formula help
 

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

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

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

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

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

formula 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

formula help
 

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

formula help
 

"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

formula 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),"#,###.")

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

formula 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),"#,###.")

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

formula help
 

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"},""))

formula help
 

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"},""))

formula help
 

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

formula help
 

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