Proper Case Function

Take a look now at a somewhat larger custom function that does more than a simple match calculation. Suppose you have a table filled with names and addresses, but for whatever reason, all the text is in uppercase (or lowercase) letters. For example, maybe the table has a Name field containing names like JOE LANE or joe lane. You want to tidy that up, but you certainly don't want to go in and edit all the data manually.

Technically, you could just use the built-in StrConv(string, vbProperCase) function to solve this problem. For example, StrConv("JOE LANE", vbProperCase) returns Joe lane. Problem solved - except that StrConv() doesn't take into consideration little quirks like the uppercase D in McDonald. StrConv("MCDONALD", vbProperCase) returns Mcdonald. Likewise, StrConv("p.o. box 123", vbProperCase) returns P.o. Box 123, which doesn't look quite right because the O should be uppercase.

To get around that, you can create your own, custom function that takes any string as its argument and then returns that string with initial caps (the first letter of each word is capitalized), just like the StrConv() function does. But your custom function can then use some If...End If statements to correct any little problems, like the Mcdonald and P.o. Box examples.

You might want to use this function to fix several fields in several tables, so you want the function to be public, like any built-in function. For starters, you need to open or create a standard module. Think up a name for your function (we call this one PCase()) and create an appropriate function. In this case, you need to pass a string (which we refer to as AnyText) to the function. The return value for the function is also a string (whatever text was passed is converted to initial caps). Listing-1 shows the function in its entirety. We take a look at how it works in a moment.

'The PCase() function accepts any string, and returns
'a string with words converted to initial caps (proper case).
Public Function PCase(AnyText As String) As String
    'Create a string variable, then store AnyText in that variable already
    'converted to proper case using the built-in StrConv() function
    Dim FixedText As String
    FixedText = StrConv(AnyText, vbProperCase)

    'Now, take care of StrConv() shortcomings

    'If first two letters are "Mc", cap third letter.
    If Left(FixedText, 2) = "Mc" Then
	FixedText = Left(FixedText, 2) & _
	    UCase(Mid(FixedText, 3, 1)) & Mid(FixedText, 4)
    End If

    'If first three letters are "Mac", cap fourth letter.
    If Left(FixedText, 3) = "Mac" Then
	FixedText = Left(FixedText, 3) & _
	    UCase(Mid(FixedText, 4, 1, )) & Mid(FixedText, 5)
    End If

    'If first four characters are P.o. then cap the "O".
    If Left(FixedText, 4) = "P.o." Then
	FixedText = "P.O." & Mid(FixedText, 5)
    End If

    'Now return the modified string.
    PCase = FixedText
End Function