Extract last word from userform textbox
I have a userform with a textbox called "strAttorneyName". I would like to be able to extract the attorney's last name to do an automated search and replace (I've got the search and replace stuff down pat; it's the extraction I'm having trouble with). I found the following code (revised to reflect my textbox's name) online whereby you can extract the FIRST word in a textbox:
Left(strAttorneyName, InStr(Me.strAttorneyName, " ") - 1)
I tried changing "Left" to "Right" in order to catch the last word in the textbox. The problem is that what is returned is only as many characters of the last name as exist in the first name. For example, if the name is "A. Johnson," what returns is "on" - only two characters, since the first name/word is only two characters. If the name is "Alex Johnson," what returns is "nson." If the name is "Andrew X.," what returns is "rew X." Does anyone know how to change the code so it returns the entire last name, regardless of the length of the first name? Thanks!
|