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

[JeffreyWeir]

It turned out that the generalized alternative version that takes the length of the string into consideration i.e. this one

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

...was slightly faster on 1000 rows of sample data as the original formula.

And my alternative formula:

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

...was slightly slower.

So my advices would be to use the generalized version, that not only is guaranteed to work with your data under greater string lengths, but is also likely to save Excel some effort. Not that you'll notice it: we are talking hundredths of a second over 1000 rows of sample data.