code.dunae.ca

Proper Name Case Excel VBA Function

Apply proper capitalization to surnames/family names/last names in Excel.

Installation

In Excel, press Alt + F11 to enter the code editor. Select Insert > Module and paste the code below into your new module file.

Back in your worksheet you can now run the NameCase method using =NameCase(A1), etc.

My version uses regular expressions which, apparently, are new to VBA. You might need a recent version of Excel to make this work.

Important!

This script does the job but can take some time processing a lot of data. I strongly recommend you use it on about 1,000 records at a time.

Contact

Written by Alex Dunae (dunae.ca, e-mail 'code' at the same domain), 2008. Excel/VBA is definitely not my strong suit—any improvements (especially performance-related) are welcome. Updates will be announced at http://twitter.com/TheCHANGELOG and on the Atom feed.

Incidentally, Webessence’s code highlighter is fantastic.

Code

For copy-and-paste action, grab the source as a text file. You can also download the Excel file with test cases. You will have to enable macros on startup.

Option Explicit

Function NameCase(Raw As String) As String
    Application.ScreenUpdating = False
    Dim RE As Object
    Dim NameParts, NamePart
    Dim Converted As String

    ' Setup the regexp object
    Set RE = CreateObject("vbscript.regexp")
    RE.IgnoreCase = True
    RE.Global = True

    ' Break names into parts and process them individually
    Set NameParts = RE.Execute(Raw)
    RE.Pattern = "(^|\s|\-)+(.[^\s^-]+)"

    Converted = ""

    ' Iterate through name parts
    For Each NamePart In NameParts
        ' Check for surname "prefixes"
        RE.IgnoreCase = True
        RE.Pattern = "(^|\s)+(van|von|der|de|la|di|al)($)"

        If RE.Test(NamePart.SubMatches(1)) Then
            ' Don't process surname prefixes, just make them lower case
           Converted = Converted & NamePart.SubMatches(0) & LCase$(NamePart.SubMatches(1))
        Else
            ' Run the name part through case conversion
            Converted = Converted & NamePart.SubMatches(0) & NameCase_Individual(NamePart.SubMatches(1), RE)
        End If
    Next
    Application.ScreenUpdating = True
    NameCase = Converted
End Function

' Convert a name-part to proper case
' RE is a regexp object, passed in so we're not always creating new objects
Function NameCase_Individual(Raw As String, RE As Object) As String
    Dim Matches, Match
    Dim Converted As String, RemainingName As String

    ' Setup the regexp object
    RE.IgnoreCase = True
    RE.Global = False

    ' Default case capitalizes the first letter and makes the rest lowercase
    Converted = UCase$(Left(Raw, 1)) & LCase$(Mid$(Raw, 2, Len(Raw) - 1))

    ' Pattern matching for names like D'Angelo, McDonald, St. John, St John, 0'Neil
    RE.Pattern = "(^|\s)+(Mc|[DO]\'|St\.|St[\.]? )([a-z]+)"
    Set Matches = RE.Execute(Converted)
    For Each Match In Matches
        RemainingName = Match.SubMatches(2)
        RemainingName = UCase$(Left(RemainingName, 1)) & Mid$(RemainingName, 2, Len(RemainingName) - 1)
        Converted = Match.SubMatches(0) & Match.SubMatches(1) & RemainingName
    Next

    ' Pattern matching for names like MacDonald, MacRae
    RE.Pattern = "(^|\s*)(Mac)([dr])([a-z -]+)"
    Set Matches = RE.Execute(Converted)
    For Each Match In Matches
        Converted = Match.SubMatches(1) & UCase(Match.SubMatches(2)) & Match.SubMatches(3)
    Next

    NameCase_Individual = Converted
End Function