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)).
0 comments:
Post a Comment