Finding the Last Occurrence of a Character in an Excel String: A User-Defined Function

I was looking for the last occurrence of a hyphen in a string of text data in a cell in Excel 2003.  I wasn't sure how many hyphens there would be, so I couldn't just do repeated searches for a hyphen from the beginning.  I vaguely recalled that there was a way to do this, but I wasn't sure what it was.  This post describes the process of answering that question.

This, I felt, was probably going to be close to the technique for counting the number of times a letter occurred in a cell.  I had searched for the answer to that one previously.  The answer here did involve the use of the SUBSTITUTE command too.  In that other case, the solution was to count the difference in length of the string before and after substituting nothing (i.e., "") for the occurrence of the character in question.

As pointed out by Aladin Akyurek, that would work here too, if you did the substitution, calculated the length difference, and then searched for the last occurrence, now that you knew how many times the character appeared in the string.   His solution appeared in a single formula, which I disfavored because it was easy to make mistakes in long formulas, hard to understand them, and hard to figure out where the mistake was.  But he did use SUBSTITUTE well, whereas I never used it, so I had something to learn there.

What I found more interesting, though, was his alternate suggestion, involving something else that I never did:  define a function and save it for future use.  I surely should have invested the time to learn Visual Basic, when it replaced the old-style BASIC that I had used for minor programming in the 1980s and somewhat thereafter, but then I guess VB changed again in the 2000s and threw a lot of its users off the trail.  Anyway, his function was one that I could actually understand, from the old BASIC days.  The steps to make it work were:

• Close all Excel files other than the one you're working on.
• Go into Tools > Macro > Visual Basic Editor > Insert > Module.
• Copy and paste this into the window:

Function Reverse(Text As String) As String
Dim i As Integer
Dim StrNew As String
Dim StrOld As String
StrOld = Trim(Text)
For i = 1 To Len(StrOld)
StrNew = Mid(StrOld, i, 1) & StrNew
Next i
Reverse = StrNew
End Function

• Go to File > Close and return to Microsoft Excel.
• Use the new REVERSE function by specifying the text to be reversed.  For my present purposes, a good use would be like this:  =FIND("-",REVERSE(A1)).

I gave that a whirl.  It worked.  But some months later, after reinstalling Windows and Excel, it didn't work.  I was not sure what had changed.  I tried going into Tools > Macro > Security and changing the level to Medium; I played with alternatives; but I still got error messages.  As another approach, I installed Morefunc and then went into Tools > Morefunc and did what it required in order to install it, and then tried Morefunc's TEXTREVERSE command.  That didn't work either.  The message I was getting was #NAME, which apparently meant that Excel was not recognizing the function (i.e., TEXTREVERSE, or whatever it was called).  Something seemed to be interfering with the operation of the Visual Basic script.  I tried suspending my antivirus program, but that wasn't the solution.  Ultimately, the solution -- getting Morefunc's TEXTREVERSE to work -- seemed to involve a combination of killing and restarting Excel, embedding Morefunc into the worksheet until it finally said "Update successful," and typing my formula in all lowercase.  There was a new problem, though:  Morefunc's TEXTREVERSE function would crash Excel 2003 when dealing with cells containing more than 127 characters.  I tried again with the REVERSE code shown above.  Now that was working too, and no 127-character limit.  It worked on a cell containing more than 1,000 characters.