Extract text between specific characters in a alphanumeric string

[excelcraziness]

Hello group,

I am looking to extract text data that is between specific underscores.
Example: ONE_TWO_THREE_FOUR_5_6_SEVEN_EIGHT_9_TEN_11

I need to extract element SEVEN. How can I specify to read between specific underscores?

Thank you!

 

[Zack Barresse]

Assuming it's always the 6th and 7th underscore and your data is in A1:

=MID(A1,FIND("~",SUBSTITUTE(A1,"_","~",6))+1,FIND("|",SUBSTITUTE(A1,"_","|",7))-1-FIND("~",SUBSTITUTE(A1,"_","~",6)))

 

[Sanjeev1976]

=mid(a1,find(char(160),substitute(a1,"_",char(160),6))+1,find(char(160),substitute(a1,"_",char(160),1))+1)

 

[FormR]

I need to extract element SEVEN.

Hi, welcome to the forum! Here is another option using Rick's formula posted here:

http://www.excelfox.com/forum/showthread.php/333-Get-Field-from-Delimited-Text-String

=TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",99)),7*99-98,99))

 

[excelcraziness]

Assuming it's always the 6th and 7th underscore and your data is in A1:

=MID(A1,FIND("~",SUBSTITUTE(A1,"_","~",6))+1,FIND("|",SUBSTITUTE(A1,"_","|",7))-1-FIND("~",SUBSTITUTE(A1,"_","~",6)))

Thank you, Zack. It worked like a champ!