Excel Tip of the Week #402 - VBA case study: Capitalising titles

Title case is similar to proper case, for which a regular Excel function already exists:

The function is just =PROPER(A1). Proper case titles the First Letter Of Each Word, regardless of what the word is. Title case is almost the same, except certain words are not capitalised:

Our function will follow these rules:

• The first and last words of the title are always capitalised
• There will be a list of words which, if they aren’t the first or last word, will not be capitalised
• If there are one or more subtitles, indicated by colons, the first/last rule will be applied to each subtitle in turn

Note that this isn’t perfect – some words are capitalised based on grammatical rules which can’t be captured by a simple rule – e.g. compare “on” in “Lectures on the Mountain-Top” and “How to Stay Switched On Forever”.  We also aren’t worrying about capitalising hyphenated words (see “Hard-learned” in the image above).

Building our function

We start by setting up our function and variables, and getting the basic approach of the function in – we will split the input into an array, using the Split function, cycle through the array to apply our formatting rules, and then rejoin the array using Join:

Public Function TITLECASE(InputStr As Variant, ExcludedWords As Range) As String

[code to check for subtitles goes here]

Dim WordList As Variant
WordList = Split(InputStr, " ")

[code to do capitalising goes here]

TITLECASE = Join(WordList, " ")

End Function

Split and Join are exact opposites, which can either convert a string into an array via a delimiting character (in our case a space), or visa versa.

Our internal code segment will need to then loop through the array and apply our rules. Arrays are indexed from 0, so here’s our basic loop structure:

For i = 0 To UBound(WordList)
    If i = 0 Or i = UBound(WordList) Then
        [code to make proper case]
    Else
        [code to check if word is on excluded list and make lower/proper case as appropriate]
    End If
Next i

We use the UBound function to identify the largest item position in the array.  For the items at the extremes (positions 0 and top), we will always use proper case; otherwise we will need to first check if our word appears on the user-supplied list of excluded words (words such as a, an, the, of, etc.).

Case-changing in VBA can be done with a function such as:

            StrConv(WordList(i), vbProperCase)

The options for StrConv’s second input include e.g. vbUpperCase, vbProperCase, and vbLowercase.

Finally we can use a match function to see if our word is in the target list or not. Here’s the complete code for this segment:

For i = 0 To UBound(WordList)
    If i = 0 Or i = UBound(WordList) Then
        WordList(i) = StrConv(WordList(i), vbProperCase)
    Else
        If IsError(Application.Match(WordList(i), ExcludedWords, 0)) Then
            WordList(i) = StrConv(WordList(i), vbProperCase)
        Else
            WordList(i) = StrConv(WordList(i), vbLowerCase)
        End If
    End If
Next i

Dealing with subtitles

How will we handle subtitles? As mentioned above, in that case, we want to apply the rules to each part of the title separately – see for example “The Lord of the Rings: The Two Towers: Extended Edition”.To do this, we’re going to add a section at the top of the code that will check for the presence of colons, using the InStr function (which returns a 0 when the searched text is not found). If we have some, then instead of using the rest of the code, we will split the string at that colon, then subject each part of the split to a recursive TITLECASE function, and then rejoin. Here’s the code:

If InStr(1, InputStr, ": ") = 0 Then
Else
    Dim Subtitle As Variant
   Subtitle = Split(InputStr, ": ")
   For i = 0 To UBound(Subtitle)
        Subtitle(i) = TITLECASE(Subtitle(i), ExcludedWords)
   Next i
    TITLECASE = Join(Subtitle, ": ")
    Exit Function
End If

The overall approach is similar to the main function itself.  Recursion means that we can use a function inside its own definition, and Excel will happily figure this out.

You can download a copy of the full function - open the VBA editor with Alt F11 to see the full code.

• Previous post - Excel on Reddit
• TOTW index
• Next time – Power Query – Merge kinds!