How to reverse search in Excel?

Question

I have a text in a worksheet like:

The girl is very beautiful

I want a formula to perform a search from right to left for the word "very", and if found then extract it to some other region of the sheet.

Note: Purpose of doing reverse search is because I want to implement it in my workbook which requires reverse search.

At least, say me how to revert the text like this :

beautiful very is girl The

Then I can do a normal search. I don't know VBA so please give some formula.

I think your choices are to use a complicated array formula, or VBA. — ashleedawg, Dec 28 '17 at 11:47
How do you plan on extracting the word once you find it? (This might affect your answer.) Do you know how you would extract if searching from left-to-right? — ashleedawg, Dec 28 '17 at 11:49

ashleedawg · Accepted Answer · 2018-01-02T07:31:49.190

VBA Function to reverse the words in text:

Public Function StrReverse(strIn As String, Optional Delimiter As String = " ") As String
    'Reverse the words in 'StrIn', split on a "Space" unless 'Delimiter' is specified
    Do While InStrRev(strIn, Delimiter) <> 0
        StrReverse = StrReverse & Delimiter & Right(strIn, Len(strIn) - InStrRev(strIn, Delimiter))
        strIn = Trim(Left(strIn, InStrRev(strIn, Delimiter) - 1))
    Loop
    StrReverse = Trim(StrReverse & Delimiter & strIn)
    If Left(StrReverse, 1) = Delimiter Then StrReverse = Right(StrReverse, Len(StrReverse) - 1)
End Function

For example, if cell A1 contains:

The girl is very beautiful

...then you could enter in another cell:

=StrReverse(A1)

...which would return:

beautiful very is girl The

To add a custom VBA function to a workbook:

Copy the code for the function you want to add to Excel (from above).
In an Excel, workbook, press Alt + F11 to open the VBA Editor (VBE).
Press Alt + I M to insert a new module.
Press Ctrl + V to paste in the code.
Press Alt + F C to return to Excel.

Edit #1:

Added optional delimiter to function above (defaults to a " " space).

Also, FindReverse (below), which allows VBA's (little-known) InStrRev function to be used on worksheets.

Public Function FindReverse(StringCheck As String, StringMatch As String, _
    Optional Start As Long = -1) As Long

    'Returns the position number of the last occurrence of 'Stringmatch" 
    'within StringCheck',  Optionally specify the position number from the
    'end to begin the search.  (-1 = Begin at the end)

    FindReverse = InStrRev(StringCheck, StringMatch, Start)

End Function

Edit #2:

_{LOL @ Myself ... I'm always telling people not to try to recreate functionality that's already built into MS Office, and it seems that I unwittingly did the same thing -- even giving it the same as the existing VBA Function.}

Built-in VBA function:

I realize that it's not identical functionality as the StrReverse function I wrote (above) but I suspect it also could have solved OP's original inquiry...

Nonetheless, I am really surprised that VBA even allows a custom function to have the same name as a built-in function!

_{How to confuse VBA:}

dawg I don't know whether it works or not, I don't have time to verify still I am accepting your answer for your efforts — Chen Guo, Dec 28 '17 at 12:55
Was thinking it might be better to add an optional delimiter in case it's not a space that's being searched for. `Public Function StrReverse(strIn As String, Optional Delimiter As String = " ") As String` It doesn't work without a space though so would need a rewrite. — Darren Bartrup-Cook, Dec 28 '17 at 13:51
...not really a rewrite; a quick fix (done). Also a function for `InStrRev`, one of a number of VBA functions that (_annoyingly_) can't normally be called directly from a worksheet. — ashleedawg, Dec 28 '17 at 20:41

How to reverse search in Excel?

1 Answers1

VBA Function to reverse the words in text:

To add a custom VBA function to a workbook:

Built-in VBA function:

Linked