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
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
Want a quick way to tell how may rows and columns you've selected? Here's what I do when I need to know that information.
Discover MoreOne way to make your worksheets less complex is to get rid of detail and keep only the summary of that detail. Here's how ...
Discover MoreIf you lose your place on the screen quite often, you might find it helpful to have not just a single cell highlighted, ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments