You could try the following macro. It will parse the data you've provided, but you'll need to check the final output for errors. For example:
• Surnames consisting of more than two words, such as 'Van Dieman' & 'Van Der Plaat' won't be handled correctly.
• Although logic to turn all names into 'proper' case and to capitalize the first letter after the 'c' in surnames prefixed by Mc or Mac, that might not be appropriate for all such surnames.
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim lRow As Long, lCol As Long, i As Long, j As Long
Dim StrGN As String, StrSN As String, StrTmp As String
With ActiveSheet.UsedRange
lRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
On Error Resume Next
For i = lRow To 1 Step -1
If .Range(.Cells(i, 1), .Cells(i, lCol)).SpecialCells(xlCellTypeBlanks).Count < lCol Then lRow = i: Exit For
Next
On Error GoTo 0
For i = 1 To lRow
If .Cells(i, 1).Value = "" Then
StrTmp = Trim(Replace(.Cells(i, 2).Value, ".", " "))
If StrTmp <> "" Then
While InStr(StrTmp, " ") > 0
StrTmp = Replace(StrTmp, " ", " ")
Wend
StrSN = Split(StrTmp, " ")(UBound(Split(StrTmp, " ")))
If StrSN Like "[SsJj][Rr]" Then
StrSN = Split(StrTmp, " ")(UBound(Split(StrTmp, " ")) - 1) & " " & StrSN
End If
StrGN = Replace(StrTmp, " " & StrSN, "")
StrSN = WorksheetFunction.Proper(StrSN)
If Left(StrSN, 2) = "Mc" Then StrSN = "Mc" & UCase(Mid(StrSN, 3, 1)) & Mid(StrSN, 4)
If Left(StrSN, 2) = "Mac" Then StrSN = "Mac" & UCase(Mid(StrSN, 4, 1)) & Mid(StrSN, 5)
.Cells(i, 1).Value = WorksheetFunction.Proper(StrGN)
.Cells(i, 2).Value = StrSN
End If
Else
StrGN = Trim(.Cells(i, 1).Value)
StrSN = Trim(.Cells(i, 2).Value)
StrSN = WorksheetFunction.Proper(StrSN)
If Left(StrSN, 2) = "Mc" Then StrSN = "Mc" & UCase(Mid(StrSN, 3, 1)) & Mid(StrSN, 4)
If Left(StrSN, 2) = "Mac" Then StrSN = "Mac" & UCase(Mid(StrSN, 4, 1)) & Mid(StrSN, 5)
.Cells(i, 1).Value = WorksheetFunction.Proper(StrGN)
.Cells(i, 2).Value = StrSN
End If
Next
End With
Application.ScreenUpdating = True
End Sub