In this Excel tutorial, you will learn how to perform reverse or right to left search in a string.
Typically, the Search and Find functions do left to right search.
Example in string “abc-pqry-123” if you want to find position of “-” then Search and Find function will output 4 from left to right.
But what if you want to find the position of 2nd or Nth instance of “-“, to extract string after 2nd “-” then this tutorial is all about this.
Two methods are shown for doing a reverse string search
- Using a combination of Excel formula
- Using VBA UDF (User Defined Function)
Reverse string search using Excel formula
In the below image, I want to extract the string after the last occurrence of the “-” and “/” characters.
In the image strings to extract are indicated by red text.
To extract string after last delimiter, 1st you need replace the last delimiter by a unique character.
cell B2 formula =SUBSTITUTE(A2,”-“,”}”,LEN(A2)-LEN(SUBSTITUTE(A2,”-“,””)))
cell B6 formula =SUBSTITUTE(A6,””,”}”,LEN(A6)-LEN(SUBSTITUTE(A6,””,””)))
In cell B2 formula part LEN(A2)-LEN(SUBSTITUTE(A2,”-“,””)) will give total occurrences of “-” delimiter.
LEN(A2)-LEN(SUBSTITUTE(A2,”-“,””)) > 11-LEN(“abcpqr123”) > 11-9 > 2
After evaluation Cell B2 formula becomes
=SUBSTITUTE(A2,”-“,”}”,2)
which means in cell A2 string substitute 2nd “-” by “}” character.
Suggested Reading Count specific words or text of a string in cell
In 2nd step find position of the unique character in the string
Cell B2 formula
=FIND(“}”,SUBSTITUTE(A2,”-“,”}”,LEN(A2)-LEN(SUBSTITUTE(A2,”-“,””))))
above evaluated to =FIND(“}”,“abc-pqr}123”) > 8
This is how reverse string search can be done using formula.
In 3rd step extraction of string after a last delimiter is done.
cell B2 formula
=RIGHT(A2,LEN(A2)-FIND(“}”,SUBSTITUTE(A2,”-“,”}”,LEN(A2)-LEN(SUBSTITUTE(A2,”-“,””)))))
above evaluates to
=RIGHT(A2,LEN(A2)-8) > RIGHT(A2,11-8) > RIGHT(A2,3) > 123
Cell B6 formula to get string after last slash “”
=RIGHT(A6,LEN(A6)-FIND(“}”,SUBSTITUTE(A6,””,”}”,LEN(A6)-LEN(SUBSTITUTE(A6,””,””)))))
Reverse search string VBA UDF
The above formula combination method is complex and requires more thought processes to achieve the result.
VBA UDF (User Defined Function) is less complex and easier than the formula combination method to reverse search string.
Cell B2 formula =extractLastString(A2,”-“)
Below is the VBA UDF code to extract string after the last delimiter.
Just paste the below code section in the standard module of the VBA IDE to use this function in the Excel cell.
Suggested Reading How to make UDF function in Excel?
extractLastString function takes two inputs
1st is a string and 2nd is a search string or delimiter.
Function extractLastString(str As String, srchStr As String) As String
extractLastString = Right(str, Len(str) - InStrRev(str, srchStr))
End Function
You can see VBA UDF function much cleaner and easier than complex formula.
All this is possible because there is an inbuilt VBA function called InStrRev.
InStrRev gives the position of a string or substring present at the rightmost side in a string.
If you test this function ?InStrRev(“abc-pqr-123″,”-“) in VBA IDE Immediate Window then you will get 8.
Note:- InStrRev gives position of rightmost substring but counting is always performed left to right.
Suggested Readings
MS Excel: Count Number of Words in a String (Words Count in a Cell)
How to insert and run VBA code in Excel?