As part of an Access data cleanse, I needed to capitalise company’s and people’s names and addresse. So I googled for a regex that would do it. Of course I found plenty that would work very nicely in other languages and on other platforms, but none that worked in VBA. I found that I was not the only person who had faced this problem, but no working solutions. So I have developed my own and present it for you here.
The first problem of course is to break the string up into words. Regex 5.5 will do this very nicely for us. What it will not do is to capitalise the words when it finds them. For this we need to process the found words with vbProperCase.
Here is the complete function for you
Public Function CapitaliseNames(ByVal strIn As String) As String
‘~~~~~
‘Name: CapitaliseNames
‘Version: 1.00
‘Purpose: capitalise the input name – handles hypens and apostrophes
‘
‘Arguments:
‘ strIn = string to capitalise
‘Return:
‘ capitalised string
‘
‘Author: David Soussan
‘Updated: 14/01/2010
‘
‘Comments:
‘ requires reference to VBScript Regular Expression 5.5
‘~~~~~
On Error GoTo ErrorHandler
Dim strReturn As String
Dim oRegEx As RegExp
Dim Matches, Match
Const strPattern As String = “\b(\w+)\b”
‘~~~~~
‘use regex to find all words
‘~~~~~
10:
Set oRegEx = New RegExp
oRegEx.Pattern = strPattern
oRegEx.Global = True
Set Matches = oRegEx.Execute(strIn)
‘~~~~~
‘capitalise each word found
‘~~~~~
20:
For Each Match In Matches
strIn = Replace(strIn, Match.Value, StrConv(Match.Value, vbProperCase))
Next
‘~~~~~
‘lowercase common terms
‘~~~~~
30:
strIn = Replace(strIn, ” And “, ” and “)
strIn = Replace(strIn, ” Of “, ” of “)
strReturn = strIn
ExitProcedure:
CapitaliseNames = strReturn
Exit Function
ErrorHandler:
strReturn = “”
Select Case iLogError(Err.Description, CODE_MODNAME, “CapitaliseNames()”, Err.Number, Erl)
Case ERR_IGNORE
Resume Next
Case Else
Resume ExitProcedure
End Select
End Function
It does of course require my error handler – so substitute your own there (and sorry about the formatting – can’t seem to get copy/paste working in wordpress 
) And do make sure that you keep the blank before and after the ‘ and ‘ n ‘ or ‘ unless you want to spoil names like Anderson and Oregon 
4 Comments
That is crazy unnecessary, dude, try this:
Public Function Capitalize(ByVal strToCapitalize As String) As String
” add a reference to excel, if you are not in excel (since you don’t mind adding references)
Capitalize = Excel.WorksheetFunction.Proper(strToCapitalize)
Capitalize = Replace(Capitalize, ” And “, ” and “, 1, , vbTextCompare)
Capitalize = Replace(Capitalize, ” Or “, ” or “, 1, , vbTextCompare)
End Function
Bill
Well now, Bill, if I’d known about that Excel function then I would have used it. So thanks for the info.
In Access VBA the equivalent using vbProperCase does not handle apostophies and hyphenated names. It was because I could not find anything like your solution out there that I came up with my own ‘unnecessary’ method.
I’m not easily irpmesesd. . . but that’s impressing me!
Awesome informations once again.! I am looking forward for your next post:)