Proper Case Conversion with Exceptions

by Allen Wyatt
(last updated April 20, 2019)

2

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Assigning Macros to Graphics

The graphics you place in a worksheet can do more than just look pretty. You can also assign macros to a graphic, which ...

Discover More

Comparing Document Versions

Do you need to compare two versions of a document to each other? Word provides a tool that can make this easy, as ...

Discover More

Grabbing the MRU List

Excel keeps track of the most recent workbooks you've used. If you want to access that information in a macro, you'll ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Easily Adding Blank Rows

Want to add a bunch of blank rows to a your data and have those rows interspersed among your existing rows? Here's a ...

Discover More

Getting Rid of All Rows Except the One for the Latest Date

As you use Excel to collect data over time, sometimes winnowing out the latest data can present a challenge. Here are a ...

Discover More

Moving Cells Using the Mouse

Want to easily move information from one cell to another? A quick way to do it is to simply drag and drop using the mouse.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven minus 2?

2019-04-23 06:25:28

David Robinson

Perhaps the easiest way to cope with punctuation would be to check each punctuation mark against the exception list, replacing with the same punctuation along with the replacement term.

For J = LBound(vWords) To UBound(vWords)

‘ Look through the exceptions list and replace any words found in it.
‘ Also replace any with trailing commas, periods etc.
For K = LBound(vExceptions) To UBound(vExceptions)
If UCase(vWords(J)) = UCase(vExceptions(K)) Then
vWords(J) = vReplacements(K)
ElseIf UCase(vWords(J)) = UCase(vExceptions(K)) & “,” Then
vWords(J) = vReplacements(K) & “,”
ElseIf UCase(vWords(J)) = UCase(vExceptions(K)) & “.” Then
vWords(J) = vReplacements(K) & “.”
ElseIf UCase(vWords(J)) = UCase(vExceptions(K)) & “?” Then
vWords(J) = vReplacements(K) & “?”
ElseIf UCase(vWords(J)) = UCase(vExceptions(K)) & “!” Then
vWords(J) = vReplacements(K) & “!”
ElseIf UCase(vWords(J)) = “(“ & UCase(vExceptions(K)) Then
vWords(J) = “(“ & vReplacements(K)
ElseIf UCase(vWords(J)) = UCase(vExceptions(K)) & “)” Then
vWords(J) = vReplacements(K) & “)”
End If
Next K
Next J


2019-04-20 12:42:38

Willy Vanhaelen

There is no need to use a For Next loop to rebuild the cell contents.

This one line of code will do the job and make the macro perform faster:

MyProper = Trim(Join(vWords, " "))

This


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.