Excel 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:
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:
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…
Get the latest Excel tips and tricks by joining the newsletter!
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.
Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!