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

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

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
Asked:
wagamama123
  • 5
  • 3
  • 2
  • +3
1 Solution
 
wagamama123Author Commented:
0
 
Saqib Husain, SyedEngineerCommented:
Please reload the file

Meanwhile you can try this formula

=MID(A1,5,LEN(A1)-12)
0
 
Saqib Husain, SyedEngineerCommented:
Or

=MID(A1,5,FIND("==",A1)-5)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
wagamama123Author Commented:
0
 
Saqib Husain, SyedEngineerCommented:
For some reason I am unable to open this file. Have you tried the above formulas?
0
 
barry houdiniCommented:
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
 
wagamama123Author 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
 
Rory ArchibaldCommented:
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
 
Arno KosterCommented:
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 )

Open in new window



0
 
barry houdiniCommented:
See example attached

regards, barry
Carolina.xls
0
 
Saqib Husain, SyedEngineerCommented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
Davy2270Commented:
Try this:
assuming the datastring is in cell A1

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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