Proper Case Conversion with Exceptions

Frank needs to convert 4,000-5,000 names daily from uppercase to proper case. The PROPER function gives him a passable result that still needs manual review and edit to cope with acronyms, etc. that should not covert. Frank's thinking is that he probably needs the function to include a look-up against some sort of an exceptions list, so he wonders if there is such a capability for case conversion in Excel.

The best way to handle this will depend on the data with which you are starting. For instance, let's say that the following are the values of two cells in your source data:

Big John's Mining, LLC
USA

If your exception is that you don't want the case of LLC changed, then you need a method that will look at parts of each cell. If your exception is that you don't want the case of USA changed, then you need a method that will evaluate the contents of each cell as a whole.

It is easier to take care of the second type of data than it is the first, so let's look at that first. The following formula relies on an exception list that you have somewhere in your workbook. This exception list needs to be set up as a named range, using the name Exceptions. If your original data is in column A, you could place this formula into cell B1 and then copy it down as far as necessary:

=IFERROR(VLOOKUP(A1,Exceptions,1,0),PROPER(A1))

Any cell that fully matches anything in your Exceptions table will end up looking exactly like the exception, and anything that doesn't fully match will end up having PROPER applied to it.

As for the first type of data (where you need to look inside each cell for exceptions), it is best to rely on a macro. The following is an example of one you could use as a starting point.

Function MyProper(ByVal r As Range) As String
    Dim vExceptions As Variant
    Dim vReplacements As Variant
    Dim vWords As Variant
    Dim iRaw As String
    Dim J As Integer
    Dim K As Integer
    Dim sTemp As String

    ' Exceptions array
    vExceptions = Array("USA", "PhD", "LLC", "and", _
      "Kentucky", "D.C.")

    ' Replacements array
    vReplacements = Array("USA", "PhD", "LLC", "and", _
      "KY", "DC")

    ' Convert the text to Proper case and store in a string
    iRaw = StrConv(r, 3)
    ' Split the words into an array
    vWords = Split(iRaw, " ")
    For J = LBound(vWords) To UBound(vWords)
        For K = LBound(vExceptions) To UBound(vExceptions)
            If UCase(vWords(J)) = UCase(vExceptions(K)) Then
                vWords(J) = vReplacements(K)
            End If
        Next K
    Next J

  ' Rebuild the cell contents
    sTemp = ""
    For J = LBound(vWords) To UBound(vWords)
        sTemp = sTemp & " " & vWords(J)
    Next J

    MyProper = Trim(sTemp)
End Function

This is a user-defined function, so you could use the following to do a conversion on your source data:

=MyProper(A1)

The speed of the macro will depend on two things: The number of times it is used in your worksheet (the number of words you need to modify) and how many exceptions you are checking for in the macro. With 4,000-5,000 words and a dozen or so exceptions being checked, the macro should still work fast enough to be acceptable. (It will certainly be faster than doing your checking by hand!)

The function relies on two arrays, vExceptions and vReplacements. It explodes the cell contents into the vWords array using the Split function. (After the Split function is executed, every element of the vWords array will contain a word, as defined by the occurrence of a space.) Each element of the vWords array is then compared to each element of the vExceptions array. If they match (or, more properly, if the uppercase version of each of them match), then the corresponding element of the vReplacements array is used in place of the original word. This approach has the added benefit of allowing you to substitute acronyms, as is done in substituting KY for Kentucky and DC for D.C.

Remember that I mentioned that this macro is only a good starting point. You will obviously need to modify it to reflect your exceptions and replacements lists. In addition, you need to understand that if there is punctuation in your original data, that punctuation is considered part of the "words" exploded by the Split function. For example, if the original data has something like "Davis, LLC, Stanton", the commas are considered part of the words they follow. (Remember that the split is made at spaces.) Thus, you will end up with "Davis, Llc, Stanton" in your result because the "LLC" in the vExceptions array will not match the "LLC," that is in the vWords array.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7840) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.