2

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!

JvdV
  • 70,606
  • 8
  • 39
  • 70
Mabel
  • 41
  • 3

3 Answers3

3

With ms365, if available, the simplest would be:

enter image description here

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.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thanks for your response. I'm getting a #NAME? when I copy and paste that into my Excel. – Mabel Aug 18 '22 at 05:51
  • Yeah @Mabel, these functions are still in the BETA-channel for insiders. You might be able to [activiate this in settings](https://insider.office.com/en-us/join/windows). But I'm also working on another answer that will not require these functions. Meanwhile, please notice the other answer down by Harun. – JvdV Aug 18 '22 at 05:52
3

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

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
1

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]

project.is4
  • 11
  • 3