Solved

# need help with excel text extraction

Posted on 2011-10-18
180 Views
I am trying to extract "Carolina" from the text below from this text string ">>> Carolina == 1.80" It must me extracted from this spot in order to be valid data. The name will vary from row to row. However, the surrounding text, >>> and == will always remain the same
0
Question by:wagamama123

Author Comment

0

LVL 43

Expert Comment

Meanwhile you can try this formula

=MID(A1,5,LEN(A1)-12)
0

LVL 43

Expert Comment

Or

=MID(A1,5,FIND("==",A1)-5)
0

Author Comment

0

LVL 43

Expert Comment

For some reason I am unable to open this file. Have you tried the above formulas?
0

LVL 50

Accepted Solution

assuming you want the text between the last >>> and the last == try this formula

=TRIM(REPLACE(LEFT(A2,LOOKUP(2^15,FIND("==",A2,ROW(INDIRECT("1:"&LEN(A2)))))-1),1,LOOKUP(2^15,FIND(">>>",A2,ROW(INDIRECT("1:"&LEN(A2)))))+2,""))

regards, barry
0

Author Comment

I am trying to extract "Carolina" from the text below from this text string ">>> Carolina == 1.80" It must me extracted from this spot in order to be valid data. The name will vary from row to row. However, the surrounding text, >>> and == will always remain the same

Here is the string:

"[5Dimes] Carolina Hurricanes vs Atlanta Thrashers >>> Atlanta Thrashers == +135
[SportsTabVIC] Carolina vs Atlanta >>> Carolina == 1.80"
0

LVL 85

Expert Comment

Try:
=TRIM(LEFT(MID(A2,FIND(">>>",A2,FIND(CHAR(10),A2,1))+3,LEN(A2)),FIND("==",MID(A2,FIND(">>>",A2,FIND(CHAR(10),A2,1))+3,LEN(A2)))-1))
0

LVL 19

Expert Comment

in general, you could use regular expressions for this but this requires in-depth information on how regex works and is not very easily adapted.

to elaborate on ssagibh,

use the find function to find the header ( >>> ) and footer ( === ) text positions and take everything in between.

=MID(A4,FIND(">>>",A4) + 3, FIND("==", A4) - FIND(">>>",A4) -3 )

0

LVL 50

Expert Comment

See example attached

regards, barry
Carolina.xls
0

LVL 43

Expert Comment

OK got the file

This formula would work for one instance of the string in the cell

=MID(A2,FIND(">>>",A2)+4,FIND("==",A2)-FIND(">>>",A2)-5)
0

LVL 43

Expert Comment

For the second instance you can use

=MID(A2,FIND(">>>",A2,FIND(">>>",A2)+1)+4,FIND("==",A2,FIND("==",A2)+1)-FIND(">>>",A2,FIND(">>>",A2)+1)-5)
0

LVL 3

Expert Comment

Try this:
assuming the datastring is in cell A1

cell B1:  =IFERROR(MID(A1,FIND("Carolina",A1),8),"")
0

## Featured Post

### Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…