In this blog post we create a reverse FIND formula to extract text after the last occurrence of a character.
Many of you reading this may already be familiar with the FIND function. You would probably have used it with LEFT or MID to locate a delimiter character, and return text before, or after that character.
In this tutorial we want to extract text after the last occurrence of a character, so want to create a reverse find effect.
Watch the Video
The Reverse FIND Excel Formula
In this example we were working with the data below in column A. We wanted to extract the text after the final hyphen character.
Each cell contains a varying number of hyphens, so we need to identify the position of the last occurrence of the character, and then extract the text.
The completed Excel formula for this reverse string search is shown below.
This formula is a monster so a detailed explanation is shown below. The video above also explains it step by step.
Find the Total Occurrences of the Character
The first job on our hands is to return the total occurrences of the hyphen character.
Our ultimate aim is to retrieve some text after the last occurrence, so we need to know how many there are in total.
The formula below uses the SUBSTITUTE function to replace all occurrences of a hyphen with nothing. A LEN function is used to count the number of characters remaining when the hyphens are removed.
This value is subtracted from the total number of characters in the cell, leaving us with the answer to the total number of hyphens.
Replace the Last Character with a Unique Marker
We will now replace the last occurrence of the hyphen with a unique character, which we can then use to easily extract the text we want.
An asterisk (*) has been used in this example as the unique marker. It could have been any text character.
The brilliant SUBSTITUTE function is used to accomplish this, with a little help from some friends.
The previous part of the formula has been used in the instance number argument of the SUBSTITUTE function, to specify to replace the last hyphen.
Extract the Text after the Final Delimiter Character
Now that we have inserted a unique marker in the position of the final delimiter character, it is time to find it and extract the text we want.
The following formula was added to what we have from the previous step. The ??? represents the formula part from the previous step.
The RIGHT function is used to extract text from the end of cell A2.
To calculate the number of characters to extract, the LEN function returns the total characters in the cell. And then the FIND function locates our unique marker character.
The position of the marker character is subtracted from the total characters in the cell.
The final formula is as below.
This can be adjusted to return text from the second from last delimiter, or anything you want really.
You now have a way of searching a string from right-to-left, in addition to the typical left-to-right search.
Related Excel Formula Tutorials
- How to use the TEXT function of Excel – with examples
- Extract the postcode from a UK address
- 4 amazing tips for the CONCATENATE function
- 4 ways to separate text in Excel