Complex text to column issue

[Scubajoe13]

Excel 2016

I was given a sheet that has 3000 plus row entries and multiple columns.  Most of the columns are fine, except for the name cell.  I need to separate it into first and last names but the information was inputted poorly.  I will give you an example of the problems I am facing by providing  a sample of the entries:

John smith

A J Smith

John Q Smith

Johnn David smith

John Smith & Jane Smith

I  have tried to use the text to column and an If-Then function, but I do not know how to nest either one to make it work

Any help would be appreciated

[#2 · Accepted solution]

@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

[#3]

It took me a few tries to enter it without getting an error,  but once I did, it worked exactly as advertised!

Thank you very much for your help.

@Scubajoe13 

[#4]

@Scubajoe13 Although your question has already been answered, I would like to demonstrate a solution using only standard user interface commands in Power Query (not for Mac). Just five simple splitting actions in the correct order and one last formatting step to capitalise the names. See it as a more advanced use of Text-to-columns. It's easily customised and works great on large sets of data.

[#5]

Thank you so much for this option. In many ways it is ismpler