Using Logic to get first and last name from cell with fulls names

[Welsh Mark3]

I have a columns of data that contains a persons full name, which I am trying to split into first and last name.
I could use the text to columns feature and use space as the delimiter, but this is not 100% perfect. Some scenarios that cause issues ar e

Mark A Edwards
Mark Edwards Sr
Mark A Edwards Sr

Does any have any advice/recommendations?

 

[tyija1995]

Hey,

When the order of names is skewed it becomes almost impossible to do this sort of thing with 1 formula,
As you say you can use a space delimiter to get the first name easy enough but whether the 2nd word is the surname or not is where the problem is. Perhaps you can split the string up and then do a comparison on all words except the first word and take the longest as the surname. Ofcourse this may not be 100% correct but it should be most of the time unless you have middle names flying around too!

 

[tcardwell]

Hi Mark,

I did a quick test using Flash Fill.
1) Create table from your data
2) Insert blank column next to the list of names
3) Type in Mark for the first couple of names and then click on Data>Flash Fill
4) Repeat using Edwards in a new colomn

The first time I tried this it worked like a charm. Then use concatenate function to join text back how you prefer.

 

[TotallyConfused]

Hello Mark
This solution is very similar to one I gave another member last November. For reference that solution can be found at:
https://www.mrexcel.com/forum/excel-questions/1078200-rearrange-names-vba-macro.html

Some assumptions have been made here.
1 ) Your data starts in row 2 with column headings above.
2 ) Your data is in column 'A'.
3 ) This sub will place the first & last names in column 'B', starting in row 2.
4 ) Your data is on Sheet1. If not, change the 'Set Ws1 = Sheet1' to your sheet number.
5 ) If you want to overwrite (destroy) your original data, then change the following code:
.Range("B" & LoopCtr) = ArrNames(0) & " " & ArrNames(1)
to read
.Range("A" & LoopCtr) = ArrNames(0) & " " & ArrNames(1)

I hope this is what you are looking for.

TotallyConfused

Sub FormatNames2()
Application.ScreenUpdating = False
   Dim ArrNames() As String
   Dim Cel As Long
   Dim DataRows As Long
   Dim LoopCtr As Long
   Dim ws1 As Worksheet: Set ws1 = Sheet1  'Change SHEET1 to your Excel sheet CodeName
With ws1
   DataRows = .Cells(Rows.Count, 1).End(xlUp).Row
   Cel = 0
       For LoopCtr = 2 To DataRows + 1
           ArrNames() = Split(.Range("A" & LoopCtr))
               For Cel = LBound(ArrNames()) To UBound(ArrNames())
                  If (Len(ArrNames(1)) = 1) Then
                      ArrNames(1) = ArrNames(2)
                  End If
                                 ' Now place First & Last names in column 'B'
                      .Range("B" & LoopCtr) = ArrNames(0) & " " & ArrNames(1)
               Next Cel
       Next LoopCtr
End With
Application.ScreenUpdating = True
End Sub