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

by Allen Wyatt
(last updated November 14, 2014)

5

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

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

Searching for Line Breaks

If you need to find where line breaks are located in cells, there are a couple of ways you can proceed. Here's a quick ...

Discover More

Ignoring Paragraph Marks when Pasting

Paste information in a worksheet, and you may end up with Excel placing it into lots of different cells. If you want it to ...

Discover More

Displaying a Hidden First Row

If you hide the first rows of a worksheet, you may have a hard time getting those rows visible again. Here's a simple way to ...

Discover More

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!

More ExcelTips (ribbon)

Using the MROUND Worksheet Function

If you want to round a value to some multiple of a whole number, you'll want to become familiar with the MROUND function. ...

Discover More

Determining the Least Common Multiple

Need to figure out the least common multiple of a range of values? It is a snap when you use the LCM function, described in ...

Discover More

Making VLOOKUP Case Sensitive

The VLOOKUP function, like other lookup functions in Excel, is not case sensitive. In other words, it doesn't matter whether ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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}] 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 6 + 0?

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

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
Subscribe

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.