Excel Formula To Find Middle Text Between 2 Outer Characters

Question

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!

Answer 1

With ms365, if available, the simplest would be:

Formula in B1:

=TEXTBEFORE(TEXTAFTER(A1,"_"),"_",-1)

---

Edit; For any version try:

=MID(LEFT(A1,FIND("|",SUBSTITUTE(A1,"_","|",3))-1),FIND("_",A1)+1,LEN(A1))

Where | would be a placeholder for any character to 'split' on, which obviously would not occur in your string otherwise.

Answer 2

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().