Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Modifying Proper Capitalization.
Like many people, Kirk copies information into Excel worksheets that originates in other places. The information that Kirk copies typically is all in CAPS, and he wants to convert it to what Excel refers to as "proper case" (only the first letter of each word is capitalized). The problem is, the PROPER worksheet function, which does the conversion, doesn't pay attention to the words it is capitalizing. Thus, words like a, an, in, and, the, and with are all initial-capped. Kirk doesn't want those words (and perhaps some others) capitalized.
There are several ways you can approach this problem. One is to use a rather long formula to do the conversion:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(PROPER($B$13);" A ";" a "); " An ";" an ");" In ";" in ");" And ";" and "); " The ";" the ");" With ";" with ")
Remember, this is all a single formula. It does the case conversion, but then substitutes the desired lowercase words (a, an, in, and, the, with). While this is relatively easy, the utility of the formula becomes limited as you increase the number of words for which substitutions should be done.
Perhaps a better approach is to use a user-defined function macro to do the case conversion for you. The following function checks for some common words that should not have initial caps, making sure they are lowercase.
Function MyProper(str As String) Dim vExclude Dim i As Integer vExclude = Array("a", "an", "in", "and", _ "the", "with", "is", "at") Application.Volatile str = StrConv(str, vbProperCase) For i = LBound(vExclude) To UBound(vExclude) str = Application.WorksheetFunction. _ Substitute(str, " " & _ StrConv(vExclude(i), vbProperCase) _ & " ", " " & vExclude(i) & " ") Next MyProper = str End Function
Words can be added to the array, and the code automatically senses the additions and checks for those added words. Notice, as well, that the code adds a space before and after each word in the array as it does its checking. This is so that you don't have the code making changes to partial words (such as "and" being within "stand") or to words at the beginning of a sentence. You can use the function within a worksheet in this way:
=MyProper(B7)
This usage returns the modified text without adjusting the original text in B7.
If you prefer, you can use a function that takes its list of words from a named range in the workbook. The following function uses a range of cells named MyList, with a single word per cell. It presumes that this list is in a worksheet named WordList.
Function ProperSpecial(cX As Range) ' rng = target Cell Dim c As Range Dim sTemp As String sTemp = Application.WorksheetFunction.Proper(cX.Value) For Each c In Worksheets("WordList").Range("MyList") sTemp = Application.WorksheetFunction.Substitute( _ sTemp, Application.WorksheetFunction.Proper( _ " " & c.Value & " "), (" " & c.Value & " ")) Next c ProperSpecial = sTemp End Function
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11267) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Modifying Proper Capitalization.
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!
When working with finances, you often need to know the rate of return on a given investment. The most common type of ...
Discover MoreVLOOKUP is an oft-used worksheet function to lookup values in a data table. If the function cannot return a value, it ...
Discover MoreExcel includes a surprising number of functions you can use to round your data. Two such functions are FLOOR and CEILING, ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-05-01 11:55:16
J. Woolley
My Excel Toolbox includes the following function to return Text converted to title case (for example, Now Is the Time for All Good Men to Come to the Aid of Their Country):
=Title(Text)
The rules for title case are imprecise (see http://capitalizemytitle.com), so the results of this function should be carefully reviewed. Here is the array of proper case words that are converted to lower case by the Title function:
Split("A An And As At But By For From In Nor Of On Or So The To Up With")
For best results, all words in Text should be lower case except words that must be capitalized like IBM or iPhone or McDonald's. For example, consider the following Text:
my "old-lady" isn't at McDonald's with her 2nd 'Apple-PC' (a.k.a. iPhone)
=Title(Text) returns the following result:
My "Old-Lady" Isn't at McDonald's with Her 2nd 'Apple-PC' (A.K.A. iPhone)
My Excel Toolbox also includes the ProperX and ProperY functions to convert to proper case like Excel's PROPER function except for letters after digit or apostrophe (ProperX) or words with an initial apostrophe (ProperY). For the example Text above:
=PROPER(Text) returns the following result:
My "Old-Lady" Isn'T At Mcdonald'S With Her 2Nd 'Apple-Pc' (A.K.A. Iphone)
=ProperX(Text) returns the following result:
My "Old-Lady" Isn't At Mcdonald's With Her 2nd 'apple-Pc' (A.K.A. Iphone)
=ProperY(Text) returns the following result:
My "Old-Lady" Isn't At Mcdonald's With Her 2nd 'Apple-Pc' (A.K.A. Iphone)
=StrConv(Text,vbProperCase) in VBA returns the following result:
My "old-lady" Isn't At Mcdonald's With Her 2nd 'apple-pc' (a.k.a. Iphone)
My Excel Toolbox also includes the TextTitleCase macro to convert all text constants in Selection to title case. Similarly, these macros are available: TextLowerCase, TextUpperCase, TextProperCase, TextSmallCaps, TextToNumber, TextFromNumber, TextTrim, and TextClean. All support Undo (Ctrl+Z).
See https://sites.google.com/view/MyExcelToolbox/
2014-11-16 02:31:38
John B.M. Henin
Hi all,
Beside the sophisticated and professional solutions you are giving here for this issue, I got a simple work around:
- Copy the text to the MS word.
- Highlight all the words you need to modify their case
- Press the CAP Toggle "Shift+F3", you will notice that their case is changing from (ALL CAPS) to (all small), press again and it's changed to (First Letter), and so on, till you are satisfied with the result.
- Copy them back to your Excel sheet.
Voila :)
2014-11-14 08:31:56
Small problem: the macro won't detect the words if right before a punctuation mark ("and,", "and.", "and!"...)
2013-04-25 08:05:41
Bryan
Allen, you sure are in love with Application.Volatile and Application.Worksheetfunction! Both of these are unnecessary and will only slow down your calculations.
Application.Volatile tells Excel to recalculate the cell anytime a recalculation is done, even if the dependant cells don't change. In other words, if Cell B1 is =MyProper(A1), by default B1 will only recalculate when A1 changes; add Application.Volatile and B1 will change when *any* cell changes! (Application.Volatile is useful for when you have a function that includes values that are affected by something which can change, but is not a dependant cell: Range.Offset, Date, etc).
Secondly, there is a built-in VBA function that replicates the Excel Substitute function: Replace. It is always faster to use a VBA function within VBA than to cross the “VBA/worksheet barrier”. As a bonus, you can force it to use vbTextCompare so you can avoid all the extra StrConv calls.
Lastly, if you are going to add spaces around every use of vExclude, then just add the spaces to the array. A better version of the first code follows.
Function MyProper(str As String) As String
Dim vExclude As Variant
Dim i As Integer
vExclude = Array(" a ", " an ", " in ", " and ", _
" the ", " with ", " is ", " at ")
str = StrConv(str, vbProperCase)
For i = LBound(vExclude) To UBound(vExclude)
str = Replace(str, vExclude(i), vExclude(i), _
Compare:=vbTextCompare)
Next
MyProper = str
End Function
2012-02-06 11:31:11
Glenn Case
See also tip 10560 for further discussion & a different macro.
2012-02-06 11:25:16
Glenn Case
Here's an expanded list of words which s/n be capitalized for Title case:
"a", "ago", "an", "and", "as", "at",
"atop", "but", "by","for", "from", "in", "into", "near", "nor", "on", "onto",
"off", "over", "of", "or", "out", "the",
"this", "to", "up", "with"
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 © 2024 Sharon Parq Associates, Inc.
Comments