Excel Formula To Find Middle Text Between 2 Outer Characters-CodePudding

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!

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

If you're set on the path you were going, this is what worked for me:

=LEFT(MID(A1,FIND("_ ",A1) 1,LEN(A1)), FIND("_ ",MID(A1,FIND("_ ",A1) 1,LEN(A1)),FIND("_ ",A1) 1)-1)

You were basically halfway there, you just needed to tell your formula to look for the second underscore. I did that be adding another FIND("_",A1) 1) before the last -1.

The other two answers above seem to be the better route. This solution is only going to work if every cell has exactly the same format [6char]_ [5char]_ [6char]_ [7char]