Get "Reversed" Field from Delimited Text String

[Rick Rothstein]

Yesterday, I posted a formula to get a specified field of text from within a delimited text string (see "http://www.excelfox.com/forum/f22/get-field-delimited-text-string-333/" for the descriptions that will be assumed in this article). It occurred to me that sometimes it is necessary to retrieve the field in reverse; that is, you might want, say, the second field from the end of the text without knowing in advance how many total fields there are. This formula will do that...

See FOLLOW UP NOTE below for a better formula...
=TRIM(MID(SUBSTITUTE(A1,delimiter,REPT(" ",99)),(2-fieldnumber+(LEN(A1)-LEN(SUBSTITUTE(A1,delimiter,"")))/LEN(delimiter))*99-98,99))

Note the delimiter (which can still be one or more characters long) appears three times in the formula, so you have to make sure you replace that keyword with the actual delimiter in all three locations. As an example, lets say you have this text in cell A1...

one - two - three - four - five - six - seven - eight - nine - ten

The delimiter here is " - " (a space/dash/space). Let's say you wanted the third field from the end (the "eight" in this example). Substituting " - " for all three "delimiter" keywords and 3 for the "fieldnumber" keyword yields this...

=TRIM(MID(SUBSTITUTE(A1," - ",REPT(" ",99)),(2-3+(LEN(A1)-LEN(SUBSTITUTE(A1," - ","")))/LEN(" - "))*99-98,99))

You might want to simplify the 2-3 part and replace it with -1, but I probably would leave the rest of the formula as is.

FOLLOW UP NOTE
-------------------------
Haseeb Avarakkan responded with a much better formula (it involves three less function calls meaning it should be more efficient) than the one I posted above. Here is his formula in a generalized format...

=TRIM(LEFT(RIGHT(SUBSTITUTE(delimiter&A1,delimiter,REPT(" ",99)),fieldnumber*99),99))

Note that there are only two delimiter replacements that need to be made (instead of the three that my formula required). Thank you for posting that Haseeb, much appreciated! 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.

Here is my example from above using Haseeb's formula instead...

=TRIM(LEFT(RIGHT(SUBSTITUTE(" - "&A1," - ",REPT(" ",99)),3*99),99))

which can be simplified to this by performing the math operation (3*99)...

=TRIM(LEFT(RIGHT(SUBSTITUTE(" - "&A1," - ",REPT(" ",99)),297),99))

[Haseeb A]

Rick, nice tip :cool:


=TRIM(MID(SUBSTITUTE(A1," - ",REPT(" ",999)),(2-3+(LEN(A1)-LEN(SUBSTITUTE(A1," - ","")))/LEN(" - "))*999-998,999))

I think this gives error, If fieldnumber > delimiter eg: 11 in the sample

I think LEFT/RIGHT may be shorter

=TRIM(LEFT(RIGHT(SUBSTITUTE(" - "&A1," - ",REPT(" ",999)),3*999),999))

Also If the fieldnumber > Total_delimiter shows as blank.

[Rick Rothstein]

I think LEFT/RIGHT may be shorter

=TRIM(LEFT(RIGHT(SUBSTITUTE(" - "&A1," - ",REPT(" ",999)),3*999),999))

Excellent! Thank you so much for posting your message. I have added a note to the original thread message citing your formula.

[JeffreyWeir]

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).


It turned out that this alternative version that takes the length of the string into consideration is slightly more efficient over 1000 rows of sample data:

=TRIM(LEFT(RIGHT(SUBSTITUTE(delimiter&text,delimiter,REPT(" ",LEN(text))),fieldnumber*LEN(text)),LEN(text)))


So my advices would be to use this 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.