Complex text to column issue

Scubajoe13 

Here is a VBA macro you can use. In this example, the names in column A are split; headers are in row 1.

Sub Test()
    SplitNames Range("A1") ' cell with the header
End Sub

Sub SplitNames(rng As Range)
    Dim r1 As Long
    Dim c As Long
    Dim r As Long
    Dim m As Long
    Dim v() As Variant
    Dim n() As String
    Dim p() As String
    r1 = rng.Row
    c = rng.Column
    m = Cells(Rows.Count, c).End(xlUp).Row
    Cells(r1, c + 1).Resize(1, 6).EntireColumn.Insert
    Cells(r1, c + 1).Resize(1, 6) = Array("First1", "Middle1", "Last1", "First2", "Middle2", "Last2")
    v = Cells(r1, c).Resize(m - r1 + 1, 7).Value
    For r = 2 To m
        p = Split(v(r, 1), "&")
        n = Split(Application.Trim(p(0)))
        v(r, 2) = n(0)
        Select Case UBound(n)
            Case 0
                ' Done
            Case 1
                v(r, 4) = n(1)
            Case Else
                v(r, 3) = n(1)
                v(r, 4) = n(2)
        End Select
        If UBound(p) >= 1 Then
            n = Split(Application.Trim(p(1)))
            v(r, 5) = n(0)
            Select Case UBound(n)
                Case 0
                    ' Done
                Case 1
                    v(r, 7) = n(1)
                Case Else
                    v(r, 6) = n(1)
                    v(r, 7) = n(2)
            End Select
        End If
    Next r
    With Cells(r1, c).Resize(m - r1 + 1, 7)
        .Value = v
        .EntireColumn.AutoFit
    End With
End Sub