Extract first instance of two-character string between underscores

[mnyankee]

I need help writing a formula that will search a string and return the first instance of two characters only, between two underscores. Probably not explaining that well but my examples are:

abcd_ef_gh_ijkl this should return ef
abc_efgh_ijk_lm_nop this should return lm

My current formula: =MID(A2,FIND("_",A2,1)+1,FIND("_",A2,FIND("_",A2,1)+1)-FIND("_",A2,1)-1)

is only returning the characters between the first two underscores. Is there a formula that will do what I am asking?

 

[hagia_sofia]

Hello, if you have access to the new REGEX functions then maybe:

=SUBSTITUTE(REGEXEXTRACT(A1,"_.{2}_"),"_","")

 

[Fluff]

Another option if you don't have regex yet

Fluff.xlsm
	A	B
1
2	abcd_ef_gh_ijkl	ef
3	abc_efgh_ijk_lm_nop	lm
Sheet5

Cell Formulas
Range	Formula
B2:B3	B2	=LET(s,TEXTSPLIT(A2,"_"),TAKE(FILTER(s,LEN(s)=2),,1))

 

[Micron]

Could also be done via vba if that is of any interest.

 

[Peter_SSs]

Another approach (more like your original) and a shorter regex option

25 03 05.xlsm
	A	B	C
1
2	abcd_ef_gh_ijkl	ef	ef
3	abc_efgh_ijk_lm_nop	lm	lm
Find 2

Cell Formulas
Range	Formula
B2:B3	B2	=MID(A2:A3,SEARCH("_??_",A2:A3)+1,2)
C2:C3	C2	=REGEXEXTRACT(A2:A3,"(?<=_)..(?=_)")
Dynamic array formulas.

 

[mnyankee]

Another option if you don't have regex yet

Fluff.xlsm
	A	B
1
2	abcd_ef_gh_ijkl	ef
3	abc_efgh_ijk_lm_nop	lm
Sheet5

Cell Formulas
Range	Formula
B2:B3	B2	=LET(s,TEXTSPLIT(A2,"_"),TAKE(FILTER(s,LEN(s)=2),,1))

While I am intrigued by your reply, I found an instance where it returns the wrong results. If my string is AA_BB_CC_DEF, your solution is returning AA when it should be BB. Seems to only happen when there are only two characters before the first underscore. Is that correctable?

 

[Fluff]

Have your tried Peter's solutions?

 

[mnyankee]

Another approach (more like your original) and a shorter regex option

25 03 05.xlsm
	A	B	C
1
2	abcd_ef_gh_ijkl	ef	ef
3	abc_efgh_ijk_lm_nop	lm	lm
Find 2

Cell Formulas
Range	Formula
B2:B3	B2	=MID(A2:A3,SEARCH("_??_",A2:A3)+1,2)
C2:C3	C2	=REGEXEXTRACT(A2:A3,"(?<=_)..(?=_)")
Dynamic array formulas.

While I do not have regex, your first solution is working perfectly. I asked ChatGPT before posting my question and it was nowhere near the correct solution. Thanks!

 

[mnyankee]

Have your tried Peter's solutions?

Yes, and it seems to be working perfectly. Thanks for your reply.

 

[Fluff]

Glad we could help & thanks for the feedback.