Converting Cells to Proper Case

by Allen Wyatt
(last updated February 14, 2015)

6

Have you ever run into people who insist on typing everything with the Caps Lock key on? In some worksheets, that may not be acceptable. Yet, there you are, with a worksheet full of text cells that are all in uppercase. How do you convert everything to upper- and lowercase, without the need to retype?

If you find yourself in this situation, the MakeProper macro may do the trick for you. It will examine a range of cells, which you select, and then convert any constants to what Excel refers to as "proper case." This simply means that when you are done, the first letter of each word in a cell will be uppercase; the rest will be lowercase. If a cell contains a formula, it is ignored.

Sub MakeProper()
    Dim rngSrc As Range
    Dim lMax As Long, lCtr As Long

    Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)
    lMax = rngSrc.Cells.Count

    For lCtr = 1 To lMax
        If Not rngSrc.Cells(lCtr).HasFormula Then
            rngSrc.Cells(lCtr) = Application.Proper(rngSrc.Cells(lCtr))
        End If
    Next lCtr
End Sub

If you would rather convert all the text in the range into lowercase, you can instead use the following macro, MakeLower().

Sub MakeLower()
    Dim rngSrc As Range
    Dim lMax As Long, lCtr As Long

    Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)
    lMax = rngSrc.Cells.Count

    For lCtr = 1 To lMax
        If Not rngSrc.Cells(lCtr).HasFormula Then
            rngSrc.Cells(lCtr) = LCase(rngSrc.Cells(lCtr))
        End If
    Next lCtr
End Sub

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2026) applies to Microsoft Excel 97, 2000, 2002, and 2003.

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

Understanding Background Printing

We click the button to print our document and seldom think of what is happening behind the scenes. Word prints documents, by ...

Discover More

Reversing Print Order

When you print a document, does it come out of the printer in the order you need? Here's how to reverse the print order to ...

Discover More

Printing Comments

Comments can be a boon when you want to annotate your worksheets. If you want, you can instruct Excel to print the comments ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (menu)

Easily Adding Blank Rows

Want to add a bunch of blank rows to a your data and have those rows interspersed among your existing rows? Here's a quick ...

Discover More

Running a Macro in a Number of Workbooks

Got a macro that you need to run on each of a number of workbooks? Excel provides a number of ways to go about this task, as ...

Discover More

Resizing Checkboxes

If you create a user form in VBA that includes checkboxes, you may want to make the checkboxes larger. You can't adjust their ...

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

2015-02-15 20:25:48

Morris Manning

Rick, your Sub ProperUpperLowerToggle() is elegant. Is there a way to add the sentence case to this code?


2015-02-15 14:57:17

Rick Rothstein

@Graham,

What an intriguing idea to make the subroutine itself Static as opposed to making the included variables Static instead. I have been programming in various forms of BASIC (the predecessor to VB) and VB/VBA since 1981 and it never dawned on me in all that time to make the procedure itself Static rather than its included variables. I will have to think about all the ramification about doing it that way, but thank you for posting it in the first place. As for your code that toggles between the three formatting options, here is my code modified to do that using a plain old Static variable for now ;-)

Sub ProperUpperLowerToggle()
Static X As Long
X = 1 + (X Mod 3)
Selection = Evaluate(Replace("IF(@="""","""",CHOOSE(" & X & ",PROPER(@),UPPER(@),LOWER(@)))", "@", Selection.Address))
End Sub


2015-02-15 05:06:26

Graham Rice

Another method that toggles through all 3 options - lower, proper and upper case.
Includes a step to stop the macro if the user selects an area beyond the currently used cells - for example the entire worksheet.
I have used this in Excel 2000, 2003 and 2010.
Also I have attached the macro to a button on the toolbar/ribbon for easier usage.

====================
Option Explicit

Static Sub CaseChange()

' Moves between Lower, Proper and Upper Case for selected fields.

On Error GoTo ErrorTrap

Dim item, shiftcase, inputFormula, cellnumbers, message

Application.ScreenUpdating = False 'Runs faster & stops screen flicker
Application.Calculation = xlManual
ActiveWorkbook.PrecisionAsDisplayed = False

shiftcase = shiftcase + 1
' initial value of shiftcase is zero if "first use" or
' "last used" value, due to "Static" statement in Sub title
If shiftcase > 3 Then shiftcase = 1
For Each item In Selection
If item.Row > Cells.SpecialCells(xlLastCell).Row Or item.Column > Cells.SpecialCells(xlLastCell).Column Then
'Prevent possible changes to large number of unused cells, e.g. down to row 65,536 !!
cellnumbers = "R" & Cells.SpecialCells(xlLastCell).Row & "C" & Cells.SpecialCells(xlLastCell).Column
inputFormula = cellnumbers
message = "Selection included cells beyond the last USED column and/or row, process stopped !!" & String(2, 10) & String(2, 13) & "The correct results may have been achieved, although these may not appear until AFTER you click 'OK'" & _
String(2, 10) & String(2, 13) & "However you may need to repeat the task, avoiding selection of any cells either :-" & String(2, 10) & String(2, 13) & _
"BELOW or TO THE RIGHT of the last USED cell. The last USED cell is "
message = message & _
Application.ConvertFormula(Formula:=inputFormula, _
fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1, toAbsolute:=xlRelative) & "."
MsgBox message
GoTo ResetParameters
End If

Select Case shiftcase
Case 1
item.Value = LCase(item.Value)
Case 2
item.Value = Application.Proper(item)
Case 3
item.Value = UCase(item.Value)
End Select
Next item

ResetParameters:
Application.Calculation = xlAutomatic
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True 'Resets screen updating
Exit Sub

ErrorTrap:
If Err.Number = 13 Then Resume Next

End Sub


2015-02-14 12:35:51

Rick Rothstein

I just noticed on my posting for making text proper case that I left off a critical letter 's' from the word "cell" and because it is missing, it may give the reader the wrong idea about my formula. Here is the post again, but with the wording changed slightly to make it perfectly clear:

Here is a one-liner macro that will make all of the text in the selection proper case...

Sub MakeProper()
Selection = Evaluate(Replace("IF(@="""","""",PROPER(@))", "@", Selection.Address))
End Sub


2015-02-14 05:50:30

Rick Rothstein

And using the same structure as I did for the MakeProper macro, here is a one-liner to make all the text in the selection lower case...

Sub MakeLower()
Selection = Evaluate(Replace("IF(@="""","""",LOWER(@))", "@", Selection.Address))
End Sub


2015-02-14 05:46:06

Rick Rothstein

Here is one-liner macro that will also make all the text in the selected cell proper case...

Sub MakeProper()
Selection = Evaluate(Replace("IF(@="""","""",PROPER(@))", "@", Selection.Address))
End Sub


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon 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.