Forum Discussion

BAWrites's avatar
BAWrites
Copper Contributor
Jan 22, 2024

Split a string by delimited characters and choose nth position in string

Looking for an excel formula that will take a string similar to this   Instance >> Chain >> TimeZone >> State >> City >> Number >> Number2   And be able to output only the first "Number" occurren...
Formulas and Functions
Harun24HR's avatar
Harun24HR
Bronze Contributor
Jan 23, 2024

BAWrites While TEXTSPLIT() will work for you. You can also use FILTERXML() for previous versions of Excel (Excel-2013 or later).

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

 Using TEXTSPLIT()...

=CHOOSECOLS(TEXTSPLIT(A1,">>"),6)

 

 

Resources