Proper Case Conversion with Exceptions

Written by Allen Wyatt (last updated May 24, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365


1

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

    ' 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
    MyProper = Trim(Join(vWords," "))
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, 2021, 2024, and Excel in Microsoft 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

Understanding At Least Line Spacing

Line spacing is used to control how close lines are to each other within a paragraph. Word allows you to specify several ...

Discover More

Changing Toolbar Location

Toolbars don't need to be tethered to the top of your program window. Although they are right at home there, you may want ...

Discover More

Extracting Targeted Records from a List

If you have a bunch of data in an Excel worksheet, you may need to work with just a subset of that data. One way to do ...

Discover More

Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!

More ExcelTips (ribbon)

Checking for a Value in a Cell

Need to figure out if a cell contains a number so that your formula makes sense? (Perhaps it would return an error if the ...

Discover More

Displaying an Input Format in a Cell

Want to show a user, in a cell, what you expect their input to look like? Unfortunately, it cannot be done natively in ...

Discover More

Adjusting a Range's Starting Point

Select a range of cells, and one of those cells will always be the starting point for the range. This tip explains how to ...

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}] (all 7 characters, in the sequence shown) 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 7 - 2?

2025-05-27 10:12:02

J. Woolley

For more on this subject, see my comment here: https://excelribbon.tips.net/T011267_Modifying_Proper_Capitalization.html


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.