1

I spent a long time searching for a simple FIND-in-reverse function in Excel and found some formulas, which were way too long for my taste (e.g. How can I perform a reverse string search in Excel without using VBA?).

So I ended up creating my own simpler one, below, which gives you the last word in a string by finding the position of the last space (or the 1st one from right to left).

=RIGHT(A1,FINDrev(” “,A1))

If you run this formula on the sentence above it (put in cell A1), it will give you the result "left)." All you need is to define a 3-row VBA UDF FINDrev() and save it in a permanently available xlam add-in:

Public Function FINDrev(Find_text As String, Within_text As String)
FINDrev = Len(Within_text)-Len(Find_text)-InStrRev(Within_text, Find_text)+1
End Function

Has anyone found a simpler solution for FIND-in-reverse?

Vasko
  • 33
  • 1
  • 7
  • There are definitely some options to do so, but a worksheet Instrrev would have been nice. Your function can be made a little simpler though, using MID and Instrrev – JvdV Oct 26 '19 at 08:31
  • 1
    Btw, another alternative is using split – JvdV Oct 26 '19 at 09:37

1 Answers1

1

You could try the VBA build-in function InStrRev().

You can find document here, https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instrrev-function .

I will make a UDF like this.

Function myRightRev(find_text As String, Within_text As String) As String
    myRightRev = Mid(Within_text, InStrRev(Within_text, find_text) + 1)
End Function
PaichengWu
  • 2,649
  • 1
  • 14
  • 28
  • Yes, thanks, MID works if the find_text is just 1 character long (there is a small typo in your formula, should be "+ 1)" at the end. If you look for something longer, e.g. "\\", the +1 at the end needs to be replaced with LEN(Find_text) – Vasko Nov 07 '19 at 09:25
  • @Vasko Thanks. It's my typo. – PaichengWu Nov 07 '19 at 09:36