How to extract "n"th word from a text string

[CRISTINA1128]

hello...i'm using excel 2016 and I need the 1st word after the number sequence. How do I accomplish this?

My text strings are as follows:
67478_06271_CHILLAXBLACK_ZG13096-06
67478_06271_OFFWEGO_ZG12711-04BOOTIEWI
67478_06271_RUBIE_ZG13574-15STRAPPYW
67478_06271_SPRAYPAINT_LACEDBOOTWITHPLAI
67478_06271_TEASE_ZG13231-01BOATIESH


** From the above list, I need to extract the following text: CHILLAXBLACK, OFFWEGO, RUBIE, SPRAYPAINT, TEASE

thank you in advance!

 

[Jonmo1]

Would it be a reasonable assumption that you want the value between the 2nd and 3rd _ ?

Try
=TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)*2,LEN(A1)))

 

[Jonmo1]

If the beginning part is always the same format (5digits_5digits_)
#####_#####_

Try

=REPLACE(LEFT(A1,FIND("_",A1,13)-1),1,12,"")

 

[Irrelevant]

Something like =MID(A1,13,SEARCH("_",A1,13)-13) A1 being where the string is stored. If the number sequence is not uniform lenght, substitute the 13 with another Search function.

Alternatively, use excel text to column tool, with "_" as the delimiter, and the 3rd column should be those values.

 

[CRISTINA1128]

thank you soooo much! these all work for me!!!! you guys are THE BEST!!! so happy! you guys made my day

 

[CRISTINA1128]

THANK YOUUUU!!

 

[JeanKim]

if your string is always from second "_" then try:

=RIGHT(H1,LEN(H1)-FIND(CHAR(1),SUBSTITUTE(H1,"_",CHAR(1),2)))

 

[Jonmo1]

You're welcome.