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

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

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

Answer 2

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