Converting Text in a String from One Case to Another

To convert text in a string from one case to another, you can use the Microsoft® Visual Basic® for Applications (VBA) StrConv function. The StrConv function converts a string to lowercase, uppercase, or proper case (initial capital letters). It takes a string and a constant that specifies how to convert the string. For example, the following code fragment converts a string to proper case:

Debug.Print StrConv("washington, oregon, and california", vbProperCase)

Running this code prints the following text to the Immediate window:

Washington, Oregon, And California

Note   The StrConv function performs other string conversions as well. For example, it converts a string from Unicode to ANSI, or vice versa. For more information about the StrConv function, search the Visual Basic Reference Help index for "StrConv function."

Most likely, you'll be about three-fourths satisfied with this result — you probably want "washington," "oregon," and "california" to be capitalized, but not "and." The word "and" is a minor word that isn't capitalized according to grammatical convention, unless it's the first word in the sentence. Unfortunately, VBA doesn't know which words to convert and which to leave alone, so it converts everything. You must manually write code to handle the cases you don't want capitalized.

If you want VBA to omit the minor words, you can define those words in a file or a table, and perform a comparison against the file or table when you convert each word. The following procedure, ConvertToProperCase, does just that — it takes a string, splits it into individual words, compares each word against a list in a text file, and converts all non-minor words to proper case.

The ConvertToProperCase procedure calls another procedure, the GetMinorWords procedure. This procedure reads a text file containing a list of minor words and returns an array of strings containing each word in the text file. The ConvertToProperCase procedure then uses the Filter function to compare each word in the string to be converted against the list of words contained in the array of minor words. If a word doesn't appear in the list, then it's converted to proper case. If it does appear, it's converted to lowercase.

Function ConvertToProperCase(strText As String) As String
   ' This function takes a string and converts it to proper
   ' case, except for any minor words.

   Dim astrText()       As String
   Dim astrWords()      As String
   Dim astrMatches()    As String
   Dim lngCount         As Long
   
   ' Return array containing minor words.
   astrWords = GetMinorWords
   
   ' Split string into array.
   astrText = Split(strText)

   ' Check each word in passed-in string against array
   ' of minor words.
   For lngCount = LBound(astrText) To UBound(astrText)
      ' Filter function returns array containing matches found.
      ' If no matches are found, upper bound of array is less than 
      ' lower bound. Store result returned by Filter function in a 
      ' String array, then compare upper bound with lower bound.
      astrMatches = Filter(astrWords, astrText(lngCount))
      If UBound(astrMatches) < Lbound(astrMatches) Then
         ' If word in string does not match any word in array
         ' of minor words, convert word to proper case.
         astrText(lngCount) = StrConv(astrText(lngCount), vbProperCase)
      Else
         ' If it does match, convert it to lowercase.
         astrText(lngCount) = StrConv(astrText(lngCount), vbLowerCase)
      End If
   Next

   ' Join the string.
   ConvertToProperCase = Join(astrText)
End Function

The ConvertToProperCase procedure calls the GetMinorWords procedure, which opens the text file that contains the list of minor words, gets a string containing all the words in the list, splits the string into an array, and returns the array. GetMinorWords calls another procedure, the GetLikelyDelimiter procedure, which finds the first likely delimiter character in the text file.

Note   To call the ConvertToProperCase procedure, you must set a reference to the Microsoft Scripting Runtime object library.

See Also

Getting the Most Out of Visual Basic for Applications | Working with Strings | Comparing Strings | Calculating String Length | Searching a String | Returning Portions of a String | Working with Strings as Arrays | Replacing Text Within a String | Working with String Variables | Working with Files