Jun 05 2021 08:27 AM
Jun 05 2021 08:27 AM
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:
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
Jun 05 2021 09:09 AMSolution
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
Jun 05 2021 10:03 PM
@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.