Returning position based values from concatenated string

[kidmullet11]

I am trying to extract a position based value from a concatenated taxonomy string. Each field in the taxonomy is separated by an underscore, so I essentially want to return the value from the field that consistently appears in say the third position in the string, ie after the second _ and before the third _.

Annoyingly the field length is not always the same (either two or three characters) but it’s position between the second and third underscore is always consistent. Is this even possible? Thanks!

 

[kweaver]

It would help if (a) you changed your profile to indicate which version of Excel you use and (b) show a few samples and expected results using XL2BB.

 

[dashiellx]

Yes, this is possible.

You need to create an array and then pull the third value from the array

    Dim varTaxonomy As Variant
    Dim strTaxonomy() As String
    Dim str3rdToxonomy As String
    
    varTaxonomy = Range("A1").Value

    strTaxonomy = Split(varTaxonomy, "_")
  
    str3rdToxonomy = strTaxonomy(2)

 

[kweaver]

If you have TEXTSPLIT:

Book1
	A	B	C
1	String	This is the first field_followed by the second_and then the third_and lastly a fourth string
2
3		Field	Result
4	One way	3	and then the third
5	Another	2	followed by the second
Sheet3

Cell Formulas
Range	Formula
C4	C4	=FILTER(TEXTSPLIT(B1,"_"),{0,0,1,0})
C5	C5	=INDEX(TEXTSPLIT(B1,"_"),,B5)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Two other options depending on your version

=TEXTAFTER(TEXTBEFORE(A2,"_",3),"_",2)

=REPLACE(LEFT(A2,FIND("^",SUBSTITUTE(A2,"_","^",3))-1),1,FIND("^",SUBSTITUTE(A2,"_","^",2)),"")