How can I split "Firstname LastnameFirstname2 Lastname2" to "Firstname Lastname"-CodePudding

I have an imported cell that has two names in it like this: "Firstname LastnameFirstname2 Lastname2". How can I split it with VBA to have one cell with "Firstname Lastname" and another cell with "Firstname2 Lastname2"? A specific example is this: split "Nick MartinezMackenzie Gore" into "Nick Martinez" and "Mackenzie Gore".

CodePudding user response：

If I understand well you need to split those words(Names) which are considered as one text, then set them in the separate cells as you describe above. If that was right try with the below code:

Sub SplitWords()
Dim TextStrng As String
Dim Result() As String

For i = 2 To 10

TextStrng = Cells(i, 1)
Result() = Split(TextStrng)
Cells(i, 2) = Result(0) & " " & Result(2)
Cells(i, 3) = Result(1)

Next i

result :

CodePudding user response：

The codes below assumed that the Input string pattern is consistent : the first character of the firstname1, lastname1, firstname2, lastname2 is a capital letter.

So the logic behind to get fn1 ln1 separate with fn2 ln2 is : on the third capital letter found in the Input string.

Sub test()
Dim rg As Range: Dim cell As Range
Dim txt As String: Dim char As String
Dim cnt As Long: Dim pos As Long

With ActiveSheet
Set rg = .Range("A2", .Range("A2").End(xlDown))
End With

cnt = 0

For Each cell In rg
    txt = cell.Value
        For pos = 1 To Len(txt)
            char = Mid(txt, pos, 1)
            If char <> " " Then If UCase(char) = char Then cnt = cnt   1
 
            If cnt = 3 Then
                cell.Offset(0, 1).Value = Left(txt, pos - 1)
                cell.Offset(0, 2).Value = Mid(txt, pos)
                Exit For
            End If
        Next pos
    cnt = 0
Next cell
 
End Sub

The process:
Create the rg variable of the input string in the active sheet column A.

Loop each row of the rg and have the value as txt variable
Then it loop to each character in txt, have the position of the char as pos variable and have the character as the value of char variable.

check, if the upper case of the char value is the same with char value itself then it create a counter in cnt variable

Once the cnt reach 3, then it means the pos(ition) is where the third char value with capital letter. So it takes the fn1 ln1 by using LEFT function and the fn2 ln2 by using the MID function.