[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 218

# need help with excel text extraction

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
wagamama123
• 5
• 3
• 2
• +3
1 Solution

Author Commented:
0

EngineerCommented:

Meanwhile you can try this formula

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

EngineerCommented:
Or

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

Author Commented:
0

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

Commented:
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 Commented:
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

Commented:
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

Commented:
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

Commented:
See example attached

regards, barry
Carolina.xls
0

EngineerCommented:
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

EngineerCommented:
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

Commented:
Try this:
assuming the datastring is in cell A1

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

## Featured Post

• 5
• 3
• 2
• +3
Tackle projects and never again get stuck behind a technical roadblock.