Split a textbox content into three separate textboxes

[kelly mort]

I have a form on which I have first name, Middle name and last name.

Then I have one textbox labeled "full name" . Which takes the combination of the names in the three textboxes.

So now, I want to split those names from the full name textbox back into their original textboxes.

The only foreign characters will be a space , a hyphen or a dot

These are the conditions :
1. A name like "nice-name" should be treated as a single word.
2. A name like "nice name" should be treated as two words.
3. A name like "A. nice name" should be treated as three words.
4. If the number of words is only two, then place the first word into first name textbox and second word into last name textbox.
5. If more than two words, then place first word into first name textbox, place last word (first from right) into the last name textbox and the remaining words into the middle name textbox.


I have tried something but seems to take me somewhere else.
I was using this to split two words but the last line failed.

Private Sub d_Click ()
      a[COLOR=#333333]= Split (d.Text,  " ")(0)
[/COLOR]b[COLOR=#333333]= Split (d.Text,  " ")(1)[/COLOR][COLOR=#333333]
[/COLOR]End Sub

I know I am doing something wrongly however my skills are limited to fix it.

I needed some powerful brains fix it for me.

Thanks

 

[Yongle]

Try this

Sub SplitName()
    Dim a, nm, N1, N2, N3, msg As String
    For Each a In Array("Peter nice-name", "nice name", "A. nice name")
        N1 = "": N2 = "": N3 = ""
        nm = Split(a)
        On Error Resume Next
        N1 = nm(0): N2 = nm(1): N3 = nm(2)
        If Err.Number > 0 Then
            N3 = N2
            N2 = ""
        End If
        On Error GoTo 0
        msg = msg & vbCr & a & vbCr & 1 & " " & N1 & vbCr & 2 & " " & N2 & vbCr & 3 & " " & N3 & vbCr
    Next
MsgBox msg
End Sub

 

[Yongle]

This is more concise

Sub SplitName()
    Dim a, nm, N1, N2, N3, msg As String
    For Each a In Array("Peter nice-name", "nice name", "A. nice name")
        nm = Split(a)
            N1 = nm(0)
            N3 = nm(UBound(nm))
            If UBound(nm) = 2 Then N2 = nm(1)
            msg = msg & vbCr & a & vbCr & 1 & " " & N1 & vbCr & 2 & " " & N2 & vbCr & 3 & " " & N3 & vbCr
            N2 = "" 
    Next
MsgBox msg
End Sub

 

[kelly mort]

Okay I have not yet tries it. I am out somewhere will be back home shortly.

Can you explain the logic behind the code to me?

Especially the array part. What is that line doing?
Besides, I can't see it pointing to any of the textboxes.

Regards
Kelly

 

[Yongle]

Explanation of code
- instead of getting the name value to split from a textbox, the code is getting 3 "test" values from an array
- instead of putting resultant values into 3 textboxes, the code is returning the values of 3 variables via a message box

To use it
The middle bit of the code is what you need. Something like this (amend the names of the textboxes to match your own)

    Dim nm
    nm = Split(TextBox1.Text, " ")
    TextBox1.Text = ""
    TextBox2.Text = nm(0)
    TextBox4.Text = nm(UBound(nm))
    If UBound(nm) = 2 Then TextBox3.Text = nm(1)

Hopefully that gives you what you need

 

[kelly mort]

Explanation of code
- instead of getting the name value to split from a textbox, the code is getting 3 "test" values from an array
- instead of putting resultant values into 3 textboxes, the code is returning the values of 3 variables via a message box

To use it
The middle bit of the code is what you need. Something like this (amend the names of the textboxes to match your own)

    Dim nm
    nm = Split(TextBox1.Text, " ")
    TextBox1.Text = ""
    TextBox2.Text = nm(0)
    TextBox4.Text = nm(UBound(nm))
    If UBound(nm) = 2 Then TextBox3.Text = nm(1)

Hopefully that gives you what you need

Great! !!!!!

This code is very sweet.
I have issues with this line:

If UBound(nm) = 2 Then TextBox3.Text = nm (1)

I change it to :

If UBound(nm) [B]>=[/B] 2 Then TextBox3.Text = nm (1)

And it's getting me the condition.

But not the correct information.

When the nm is more than two, it places only the nm (1) in the textbox3 which shouldn't be the case.

After placing nm (0) in textbox2 and nm (UBound(nm)) in textbox4, I want whatever is in between nm (0) and nm (UBound(nm)) placed inside the textbox3.

Thanks very much for the script.

 

[Yongle]

Try this
- variables re-introduced and commentarry added to make code easier to understand
- delete this line to retain "dot"

    txt = Replace(txt, ".", " ")

The code has been made more robust to try to anticipate "unexpected" values

Trim removes double spaces, leading spaces & trailing spaces
- an accidental double space in the original name would add an extra item to the (split) array, which would be unhelpful

If there is only one name it is placed in First Name (it should be required but is there to prevent code failing)

To allow for multiple middle names, everything except First Name and Last Name is placed in the other textbox

    Dim tb, nm, txt As String, a As Long, N3 As String, N2 As String, N1 As String
[COLOR=#006400][I] 'clear result textboxes[/I][/COLOR]
    For Each tb In Array(TextBox2, TextBox3, TextBox4)
        .Text = ""                                 
    Next tb
[COLOR=#006400][I]'get string from full name[/I][/COLOR]
    txt = TextBox1.Text
    txt = Replace(txt, ".", " ")                    [I][COLOR=#008080]'remove "." and replace with space[/COLOR][/I]
    txt = WorksheetFunction.Trim(txt)
    If txt = vbNullString Then Exit Sub
[COLOR=#006400][I]'split full name[/I][/COLOR]
    nm = Split(txt)
[COLOR=#006400][I]'place split values inside variables[/I][/COLOR]
    N1 = nm(0)
    If UBound(nm) > 0 Then N3 = nm(UBound(nm))      [COLOR=#008080][I]'prevents code failing if only one name[/I][/COLOR]
    If UBound(nm) > 1 Then                         [I][COLOR=#008080] 'allow for multiple middle names[/COLOR][/I]
        For a = 1 To UBound(nm) - 1
            N2 = Trim(N2 & " " & nm(a))
        Next
    End If
[COLOR=#006400][I]'place values in result textboxes and clear full name[/I][/COLOR]
    TextBox2.Text = N1
    TextBox3.Text = N2
    TextBox4.Text = N3
    TextBox1.Text = ""

 

[kelly mort]

I get error on this line:
For Each tb In Array(TextBox2, TextBox3, TextBox4)
.Text = ""
Next tb




Says invalid or unqualified reference

And highlights the .Text=""

 

[kelly mort]

I just fixed it

tb.Text ="""

 

[Yongle]

Have you spotted that I replied to your post#6 question with enhanced code? (see post#7)