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/ge...xt-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))