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:

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

    str = StrConv(str, vbProperCase)
    For i = LBound(vExclude) To UBound(vExclude)
        str = Application.WorksheetFunction. _
          Substitute(str, " " & _
          StrConv(vExclude(i), vbProperCase) _
          & " ", " " & vExclude(i) & " ")
    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:


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


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


Extracting INCLUDEPICTURE File Names

If you use the INCLUDEPICTURE field to add images to your document, you may love the macro in this tip. It allows you to ...

Discover More

Reverse Numbered Lists

Adding numbered lists to your document is a snap; Word provides tools to add them immediately. What Word doesn't do is ...

Discover More

Keeping Centered Headers and Footers Centered

Headers and footers provide a nice final touch for your printed documents. If you want to expertly align text in those ...

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)

Random Numbers in a Range

Excel provides several different functions that you can use to generate random numbers. One of the most useful is the ...

Discover More

Converting Radians to Degrees

When applying trigonometry to the values in a worksheet, you may need to convert radians to degrees. This is done by ...

Discover More

Making VLOOKUP Trigger a Macro

VLOOKUP is an oft-used worksheet function to lookup values in a data table. If the function cannot return a value, it ...

Discover More

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

View most recent newsletter.


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 3 + 7?

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):
The rules for title case are imprecise (see, 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


Small problem: the macro won't detect the words if right before a punctuation mark ("and,", "and.", "and!"...)

2013-04-25 08:05:41


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), _

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"

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

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.