I am trying to figure out a formula to find the value between two underscore characters. I am trying to extract the "12345_XYZXYZ" from the below string. The 12345 and XYZXYZ are variable in that the actual data im working with the sections could be any amount of characters but will always be between the 2 outer underscores with an underscore in between them. I was hoping to use the underscores as an indicator but the fact there are 3 underscores and one is in the middle of the string is throwing me off:
Cell A1: ABCDEF_12345_XYZXYZ_-423423
the formula I’ve tried so far is in B2 and it only returns the 12345: =LEFT(MID(A1,FIND("",A1)+1,LEN(A1)),FIND("",MID(A1,FIND("_",A1)+1,LEN(A1)))-1)
Thanks for your help!
>Solution :
If you have TEXTJOIN() then can try FILTERXML() with textjoin function.
=TEXTJOIN("_",TRUE,FILTERXML("<t><s>"&SUBSTITUTE(A1,"_","</s><s>")&"</s></t>","//s[position()=2 or position()=3]"))
You can read this article from JvdV for Details about FILTERXML().
