Get Field from Delimited Text String

[JeffreyWeir]

Hi Rick. Re your comment

The only caution is that the text in A1 must be less than 100 characters (that is what the 99 establishes). If your text is longer, then you would need to change the three 99's in the formula to a number equal to, or larger than, the maximum number of characters the text in A1 can have (change the 98 to one less than that number).

...I guess one could always set that multiplier dynamically e.g.:

=TRIM(MID(SUBSTITUTE(Text,Delim,REPT(" ",LEN(Text))),element*LEN(Text)-(LEN(Text)-1),LEN(Text)))

There's a few more function calls involved, so I don't know what it means in terms of efficiency.

Here's a formula I use, by the way:

=MID(Text,FIND("|",SUBSTITUTE(Delim&Text&Delim,Delim,"|",element)),SUM(FIND("|",SUBSTITUTE(Delim&Text&Delim,Delim,"|",element+{0,1}))*{-1,1})-LEN(Delim))

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA