CODEWITHSUNDEEP
vbaexcel
user3399217
user3399217

Reputation:

Splitting a string with a comma as delimiter

I need a text box where you enter a name and it then separates the name into a first and last name and displays it in 2 text boxes. It must support entry in the form of "FIRST LAST" and "LAST, FIRST".

I have the FIRST LAST done.

This is what I have for the LAST, FIRST:

Private Sub inputText_Change()

End Sub

Private Sub Label1_Click()

End Sub

Private Sub retrieveinput_Click()
FullName = inputText.Text
Dim NameArray() As String

If FullName.Contains(",") Then
    NameArray = Split(FullName, ",")
    First = NameArray(1)
    Last = NameArray(0)
Else
    NameArray = Split(FullName)
    First = NameArray(0)
    Last = NameArray(1)
End If

TextBox2.Text = First
TextBox3.Text = Last

End Sub

This is giving an error that says "Object Required" at

If FullName.Contains(",") Then

Upvotes: 2

Views: 2430

Answers (1)

paul bica
paul bica

Reputation: 10705

VBA code:

Option Explicit

Private Sub retrieveinput_Click()
    Dim nameArray As Variant, full As String, first As String, last As String

    full = inputText.Text

    If Len(full) > 0 Then                   'if not empty

        nameArray = Split(full, ",")        'attempt to split on comma

        If UBound(nameArray) < 1 Then       'if comma doesn't exist (1 element in array)

            nameArray = Split(full)         'attempt to split by space (" ")

            If UBound(nameArray) > 0 Then   'if at least one space exists
                first = nameArray(0)        'FIRST_1 FIRST_2 LAST doesn't work
                last = nameArray(1)         'multiple last names won't work either
            Else
                first = full                'one word only
            End If
        Else                                'comma exists so last name is first in array
            first = Trim(nameArray(1))      'remove first space after comma
            last = Trim(nameArray(0))
        End If
    End If
    TextBox2.Text = first
    TextBox3.Text = last

    'Debug.Print "First Name: """ & first & """, Last Name: """ & last & """"

    'Input 1: ""                            -> First Name: "", Last Name: ""
    'Input 2: "FIRST LAST"                  -> First Name: "FIRST", Last Name: "LAST"
    'Input 3: "LAST, FIRST"                 -> First Name: "FIRST", Last Name: "LAST"
    'Input 4: "FIRST1 FIRTS2 LAST1 LAST2"   -> First Name: "FIRST1", Last Name: "FIRTS2"
    'Input 5: "FIRST"                       -> First Name: "FIRST", Last Name: ""
End Sub

Upvotes: 1

Related Questions