Parsing 'FullName' column

macropod · #2 05-03-2014, 11:51 PM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
keep each item/cell in column A associated with a group in column C	ht889	Excel	1	02-25-2014 12:36 PM
Columns. How to move the left column so it's to the right of the 'right' column ?	Vit	Word	9	11-21-2012 12:57 PM
Automatically enter date into a column and make that column read only	Mr Davo	Excel	1	10-29-2012 01:07 AM
Can I change the horizontal scrollbar to scroll smoothly rather than column by column	carpat	Excel	0	01-10-2012 09:34 AM
Need to search a column for a macth and return a result from a third column	pdfaust	Excel	2	02-03-2011 03:02 PM