LEN and Right, Left, Mid

[Ted Metro]

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

 

[Frank Kabel]

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

 

[ChrisJ]

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

 

[Peo Sjoblom]

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

 

[Ken Wright]

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

 

[Arvi Laanemets]

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]

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)