How to get the nth text after a delimiter in excel

Question

I have the following values

animal_cat
dog_bone_husky

i want to get only the second text after the first underscore

in this case i only want to get cat for B1

and bone for B2

im using this formula

=TRIM(MID(A2,FIND("_",SUBSTITUTE(A2,",","_",3))+1,255))

the problem is for my second row it also gets all the text after the first delimiter in this case it shows bone_husky

is there a way to only get the second text?

Answer 1

Quick and dirty version:

=INDEX(TEXTSPLIT(A1,"_"),2)

Answer 2

A1 =TRIM(MID(A1,FIND("",SUBSTITUTE(A1,",","",3))+1,255))    ~~> cat
A2 =TRIM(MID(A2,FIND("",SUBSTITUTE(A2,",","",3))+1,255))    ~~> bone

Answer 3

Perhaps also worth noting is the TEXTSPLIT excel function

https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7

e.g. for input-strings with underscore in column A ("Input" in my e.g.), use formula =TEXTSPLIT(A2,"_",,,,) in column B ("output" in my e.g.) and fill down.

Then leverage the nth output column, the second in your case (column C in my e.g.)

You can use INDEX to get this Nth item, e.g.

=INDEX(TEXTSPLIT(A2,,"_"),2)

Answer 4

Say A1 contains:

alpha_beta_gamma_delta_zeta_eta_theta

to get alpha use: =TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",999)),1*999-998,999))
to get beta use: =TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",999)),2*999-998,999))
to get gamma use: =TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",999)),3*999-998,999))
to get delta use: =TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",999)),4*999-998,999))
to get zeta use: =TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",999)),5*999-998,999))

etc.

If you enter:

=TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

in B1 and copy across, you will get the equivalent to Text-to-Columns in formula form:

Based on:

Rick Rothstein

Answer 5

Yes there is (multiple ways). For example as shown here:

=FILTERXML("<t><s>"&SUBSTITUTE(A1,"_","</s><s>")&"</s></t>","//s[position()=2]")