CODEWITHSUNDEEP
excelexcel-formula
Patrick
Patrick

Reputation: 11

Excel Parsing String in Cell

I'm trying to parse a string column in my Excel file.

The column looks like this:

ABC for XYZ123 at ABC
ABC for SUJ132 at DCB
DCE for UEJ958 at PLD

I want to create a formula that parses everything after "for" and before "at".

Expected Result:

XYZ123
SUJ132
UEJ958

I have this formula: =MID(A2,(FIND("for",A2,1)+4),FIND("at",A2,2)) But this is resulting in:

XYZ123 at ABC
SUJ132 at DCB
UEJ958 at PLD

Any help?

Upvotes: 0

Views: 542

Answers (3)

jshea
jshea

Reputation: 13

Try this formula:

=MID(A2,FIND("for ",A2)+4,FIND(" at",A2)-FIND("for ",A2)-4)

FIND("for ",A2)+4 specifies the starting position, and FIND(" at",A2)-FIND("for ",A2)-4 identifies the number of characters between.

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

If you have Windows Excel 2013+, you can also use the FILTERXML function:

=FILTERXML("<t><s>" & SUBSTITUTE(A1," ","</s><s>") & "</s></t>","//s[preceding::*='for' and following::*='at']")

enter image description here

Upvotes: 0

Patrick
Patrick

Reputation: 11

=MID(A2,(FIND("for",A2,1)+4),5). If the string to extract is of variable length, then =MID(A2,(FIND("for",A2,1)+4),FIND("at",A2,2)-FIND("for",A2,1)-5)

Upvotes: 0

Related Questions