How to do reverse string search (find) in Excel?

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. 

Extract string after last delimiter in Excel

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

Replace last delimiter by unique character in Excel

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. 

Reverse string search formula in Excel

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,”\”,””)))))

Extract string after last delimiter Excel formula

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,”-“)

Reverse search string VBA UDF in Excel

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.

InStrRev VBA function to find string position from right in Excel

Leave a Comment

Share via
Copy link
Powered by Social Snap