ICAEW.com works better with JavaScript enabled.

Tip of the Week

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

Author: David Lyford-Smith

Published: 13 Jul 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to the Excel Tip of the Week! This week, we have a Developer level post in which we’re going to look at how to build a custom function that will convert text into title case.

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

screenshot from Ecxel spreadsheet
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:
screenshot from Excel spreadsheet

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]
        [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)
        If IsError(Application.Match(WordList(i), ExcludedWords, 0)) Then
            WordList(i) = StrConv(WordList(i), vbProperCase)
            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
    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.

Excel community

This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.

Excel polaroid