by Allen Wyatt
(last updated April 20, 2019)
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:
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:
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Want to select all the data in a contiguous section of a worksheet? The shortcut discussed in this tip makes it very easy.Discover More
Do you need to know how many words are in a range of cells? Excel provides no intrinsic way to count the words, but you ...Discover More
If you type information into a workbook, you may want to make sure that what you type is always stored in uppercase. ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.