#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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 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 |
#3
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
Thank you!
Hey, I tried d4okeef's first suggestion and it works great! Thanks to both for your help.
|
Tags |
extract, textbox |
|
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 |