How to Do a Reverse String Search in Excel Using FIND

Reverse String Search LeadExcel has some great functions available for working with long phrases (strings). It has RIGHT, LEFT, and TRIM to manage sub-strings. It also has FIND and SEARCH to look for specific characters or sub-strings inside a phrase. The only problem is that FIND and SEARCH look from left to right in a string. If you need to look for something starting at the end of a string, there isn’t a good tool built into Excel to do so. Fortunately, it is possible to build a formula to do just that – a reverse string search. Here’s how…

 The Normal FIND Function

Let’s assume we have a simple string of characters we want to manipulate:

The quick brown fox jumps over the lazy dog.

If we were looking to identify the first word in the string, we could use a basic FIND function. The syntax for FIND is as follows:

=FIND(find_text, within_text, [start_num])

Assuming our string is in cell A1, the FIND function that locates the first space in the sentence is as follows:

=FIND(" ",A1)

The function returns 4. To get the first word in the sentence, we can use a LEFT function. The syntax for the LEFT function is as follows:

=LEFT(text, [num_chars])

The LEFT function that gives us the first word is as follows:

=LEFT(A1, FIND(" ",A1)-1)

It returns The. But what if we want the last word in the sentence? For that, we need to reverse the FIND function…