Excel Parsing String in Cell-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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