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.

# Modifying Proper Capitalization

Written by Allen Wyatt (last updated March 30, 2023)
This tip applies to Excel 2007 and 2010

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:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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.

##### 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

Selecting a Group of Words

Want to select a chunk of text in a document? Perhaps the easiest way to do this involves using the mouse in conjunction ...

Discover More

Repeating Rows for a Table Footer

Word allows you to specify rows that should be repeated at the top of a table when that table extends beyond the bottom ...

Discover More

Declaring Variables

Macros depend on the use of variables to do their work. This tip examines how variables are declared in a macro, using ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

##### More ExcelTips (ribbon)

Using the ABS Function

Need to find the absolute value of a number? That's where the ABS function comes into play.

Discover More

Finding Unique Rows Based on Two Columns

Using the UNIQUE function you can derive unique values from a range of cells. By expanding the range of cells accessed by ...

Discover More

Establishing a FLOOR and CEILING

Excel includes a surprising number of functions you can use to round your data. Two such functions are FLOOR and CEILING, ...

Discover More
##### Subscribe

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

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 5 + 0?

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).

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

balthamossa2b

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

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"

##### 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.