I have the following string:
AA_Foo_Bar - ABC123.blah.comI want to retrieve Bar. i.e. the text between the second underscore and the space following that underscore.
I have this but it's not quite correct (where A2 is the value):
=MID(A2, SEARCH("_",A2) + 1, SEARCH("_",A2,SEARCH("_",A2,)+1) - SEARCH(" ",A2) - 1)2 Answers
Answer
=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"_",CHAR(1),2))+1,FIND(CHAR(1),SUBSTITUTE(A1," -",CHAR(1),1))-FIND(CHAR(1),SUBSTITUTE(A1,"_",CHAR(1),2)))Explanation
First find the index of the second underscore (Answer = 7)
=FIND(CHAR(1),SUBSTITUTE(A1,"_",CHAR(1),2))Next find the index of the first instance of space + dash (" '") (Answer = 11):
=FIND(CHAR(1),SUBSTITUTE(A1," -",CHAR(1),1))Now grab the string from the first index (+1 to chop off the underscore) which means:
=MID(A1,7+1,11-7)Now just replace 7 with FIND(CHAR(1),SUBSTITUTE(A1,"_",CHAR(1),2)) and 11 with FIND(CHAR(1),SUBSTITUTE(A1," -",CHAR(1),1)) and Bob's your Mother's Brother.
Try:
=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1," - ","_"),"_","</s><s>")&"</s></t>","//s[3]")A little less elegant (IMO) could be:
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1," - ","_"),"_",REPT(" ",100)),200,100))
