Extract last word from userform textbox

68Monkey · #1 06-15-2018, 11:37 AM

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!

d4okeefe · #2 06-15-2018, 12:59 PM

Code:

Sub get_last_word_in_string()
    Dim strAttorneyName: strAttorneyName = "My name is Smith"
    Dim arr, last_name As String
    arr = Split(strAttorneyName, " ")
    last_name = arr(UBound(arr))
End Sub

Or, the rather complicated ...

Code:

Sub get_last_word_in_string()
    Dim strAttorneyName: strAttorneyName = "My name is Smith"
    Dim last_name as String
    last_name = Right(strAttorneyName, _
        Len(strAttorneyName) - _
        InStrRev(strAttorneyName, " ", _
        Len(strAttorneyName) - 1))
End Sub

macropod · #3 06-15-2018, 03:40 PM

Try:
Right(strAttorneyName, Len(strAttorneyName)- InStrRev(strAttorneyName, " "))
or:
Split(strAttorneyName, " ")(Ubound(Split(strAttorneyName, " "))

Do note, however, no such approach will return names that have two or more words (e.g. St James, Van Heusen). For that, you really need to have separate textboxes for the Attorney given & family names.

68Monkey · #4 06-16-2018, 07:50 AM

Hey, I tried d4okeef's first suggestion and it works great! Thanks to both for your help.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Spell Check TextBox in UserForm	rjmiller	Word VBA	6	12-17-2018 09:32 PM
UserForm textbox exit event activated with navigation buttons...why? help?	orozvik@yahoo.com	Excel Programming	2	05-08-2015 02:50 AM
Can't use RTF Textbox in Userform (Word2007)	dherr	Word VBA	2	03-16-2015 07:50 AM
Userform VBA Textbox Calculation	MarkAn	Word VBA	2	08-15-2014 06:50 AM
Number format in Textbox on userform	officeclerk	Excel Programming	2	04-17-2012 01:23 AM