LEN and Right, Left, Mid
 

I have this cell that I'm pulling down from the web --

At 10:12AM ET: 80.00 Up 0.01 (0.01%)

I want to isolate the price, which is 80.00.

Because the # of characters could change, I think the easiest way is to
write a formula to grab the characters to the right of "ET: ".

Now because the price could be single digits to several digits, I think the
formula needs to grab everthing before the decimal and then grab the two
characters after the decimal.

How can I write a formula to grab every character after ET: but before the
decimal, and then grab the decimal and two characters following the decimal?

Ted

Hi
try
=LEFT(TRIM(MID(A1,FIND("ET:",A1)+4,255),FIND("
",TRIM(MID(A1,FIND("ET:",A1)+4,255))-1)

--
Regards
Frank Kabel
Frankfurt, Germany

Ted Metro wrote:
I have this cell that I'm pulling down from the web --

At 10:12AM ET: 80.00 Up 0.01 (0.01%)

I want to isolate the price, which is 80.00.

Because the # of characters could change, I think the easiest way is
to write a formula to grab the characters to the right of "ET: ".

Now because the price could be single digits to several digits, I
think the formula needs to grab everthing before the decimal and then
grab the two characters after the decimal.

How can I write a formula to grab every character after ET: but
before the decimal, and then grab the decimal and two characters
following the decimal?

Ted

Assuming your data is in cell "A3", your formula would look as follows:
"=RIGHT(LEFT(A3, FIND(".", A3)+2), LEN(LEFT(A3, FIND(".", A3)+2))-FIND("ET:
", A3)-3)"

"Ted Metro" wrote:

I have this cell that I'm pulling down from the web --

At 10:12AM ET: 80.00 Up 0.01 (0.01%)

I want to isolate the price, which is 80.00.

Because the # of characters could change, I think the easiest way is to
write a formula to grab the characters to the right of "ET: ".

Now because the price could be single digits to several digits, I think the
formula needs to grab everthing before the decimal and then grab the two
characters after the decimal.

How can I write a formula to grab every character after ET: but before the
decimal, and then grab the decimal and two characters following the decimal?

Ted

Assuming there is always ET:space followed by the price and a space

=--LEFT(MID(A1,FIND("ET",A1)+4,255),FIND(" ",MID(A1,FIND("ET",A1)+4,255)))

format as currency

Regards,

Peo Sjoblom

"Ted Metro" wrote:

I have this cell that I'm pulling down from the web --

At 10:12AM ET: 80.00 Up 0.01 (0.01%)

I want to isolate the price, which is 80.00.

Because the # of characters could change, I think the easiest way is to
write a formula to grab the characters to the right of "ET: ".

Now because the price could be single digits to several digits, I think the
formula needs to grab everthing before the decimal and then grab the two
characters after the decimal.

How can I write a formula to grab every character after ET: but before the
decimal, and then grab the decimal and two characters following the decimal?

Ted

=LEFT(MID(A1,FIND("ET: ",A1)+4,LEN(A1)),FIND(" ",MID(A1,FIND("ET:
",A1)+4,LEN(A1)))-1)

But it returns a string and NOT a number - Did you need it to be numeric?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Ted Metro" wrote in message
...
I have this cell that I'm pulling down from the web --

At 10:12AM ET: 80.00 Up 0.01 (0.01%)

I want to isolate the price, which is 80.00.

Because the # of characters could change, I think the easiest way is to
write a formula to grab the characters to the right of "ET: ".

Now because the price could be single digits to several digits, I think the
formula needs to grab everthing before the decimal and then grab the two
characters after the decimal.

How can I write a formula to grab every character after ET: but before the
decimal, and then grab the decimal and two characters following the decimal?

Ted

Hi

You imported a table row as text string. You can use Excel's TextToColumns
feature here. Select cell(s) with string(s) (you must have enough free
adjacent columns to right of it) and then from Data menu 'Text to Columns'.
Select 'Delimited', set space as delimiter, and finish it. You get all parts
in different columns, i.e. for your example string 7 entries:
At 10:12AM ET: 80.00 Up 0.01 (0.01%)


Arvi Laanemets


"Ted Metro" wrote in message
...
I have this cell that I'm pulling down from the web --

At 10:12AM ET: 80.00 Up 0.01 (0.01%)

I want to isolate the price, which is 80.00.

Because the # of characters could change, I think the easiest way is to
write a formula to grab the characters to the right of "ET: ".

Now because the price could be single digits to several digits, I think
the
formula needs to grab everthing before the decimal and then grab the two
characters after the decimal.

How can I write a formula to grab every character after ET: but before the
decimal, and then grab the decimal and two characters following the
decimal?

Ted

I used a similar formula and also figured it out...

RIGHT(MID(B18,12,FIND(".",B18)-FIND("ET:",B18)),LEN(MID(B18,12,FIND(".",B18)-FIND("ET:",B18)))-4)&MID(B18,FIND(".",B18),3)

"Ted Metro" wrote:

I have this cell that I'm pulling down from the web --

At 10:12AM ET: 80.00 Up 0.01 (0.01%)

I want to isolate the price, which is 80.00.

Because the # of characters could change, I think the easiest way is to
write a formula to grab the characters to the right of "ET: ".

Now because the price could be single digits to several digits, I think the
formula needs to grab everthing before the decimal and then grab the two
characters after the decimal.

How can I write a formula to grab every character after ET: but before the
decimal, and then grab the decimal and two characters following the decimal?

Ted