BUY EXCEL BOOKS ONLINE: 1. VBA & Macros      2. VBA for Modelers      3. Excel 2013 VBA and Macros     
4. Excel VBA for Dummies      5. Excel with VBA & .NET      6. Mastering VBA      7. Excel 2013 Programming

Change Capital Letters to Small (Upper, lower, Proper Case conversion using Excel/VBA)

This post contains VBA macro example on how to convert the words, phrase or sentence from 'lower' to 'UPPER'(Capital letters) 'Proper' or 'Sentence' case or vice-versa using Microsoft Visual Basic for Applications in Microsoft Excel. We will loop through all cells using FOR-NEXT and will set the case as required.

If you are new to the VBA macros in MS Excel, follow these instructions:

  1. Create a new or blank Excel workbook.
  2. Press ALT+F11 to open the Visual Basic Editor in MS Excel.
  3. Go to 'Insert' in the Menu --> Select 'Module'.
  4. Copy and Paste any or all of the following macro in the new module sheet.

TO UPPER CASE
Sub Change_to_Upper_Case()
' This module will change case of selected cells
' to UPPER CASE or CAPITAL letters.
     On Error Resume Next
     Dim MyCell As Range
     For Each MyCell In Selection.Cells
          MyCell.Value = UCase(MyCell.Value)
     Next
     On Error GoTo 0
End Sub

to lower case
Sub ChangeLCase()
' This module will change case of selected cells to lower case.
     On Error Resume Next
     Dim MyCell As Range
     For Each MyCell In Selection.Cells
          MyCell.Value = LCase(MyCell.Value)
     Next
     On Error GoTo 0
End Sub

To Proper Case (First letter of each word is capital in the sentence)
Sub ChangePCase()
' This module will change case of selected cells to Proper Case.
     On Error Resume Next
     Dim MyCell As Range
     For Each MyCell In Selection.Cells
          MyCell.Value = WorksheetFunction.Proper(MyCell.Value)
     Next
     On Error GoTo 0
End Sub

To sentence case (Only first letter of first word is capital in the sentence.)
Sub ChangeSCase()
' This module will change case of selected cells to Sentence case.
     On Error Resume Next
     Dim MyCell As Range
     For Each MyCell In Selection.Cells
          If Len(MyCell.Value) >= 2 Then
               MyCell.Value = UCase(Left(MyCell.Value, 1)) & _
                    LCase(Right(MyCell.Value, (Len(MyCell.Value) - 1)))
          End If
     Next
     On Error GoTo 0
End Sub

To ToGgGlE CaSe (Alternate letters are Capital and small.)
Sub ChangeTCase()
' This module will change case of selected cells to ToGgLe CaSe.
  Dim MyCell As Range
  Dim i As Integer
  On Error Resume Next
  For Each MyCell In Selection.Cells
     If Len(MyCell.Value) >= 2 And IsNumeric(MyCell.Value) = False And _
     IsEmpty(MyCell.Value) = False And IsNull(MyCell.Value) = False Then
          For i = 1 To Len(MyCell.Value) Step 2
               MyCell.Characters(i, 1).Text = UCase(MyCell.Characters(i, 1).Text)
          Next
          For i = 2 To Len(MyCell.Value) Step 2
               MyCell.Characters(i, 1).Text = LCase(MyCell.Characters(i, 1).Text)
          Next
     End If
  Next
  On Error GoTo 0
End Sub

How to use?
To run these macros, simply press F5 while placing the cursor inside the macro code.

OR From MS Excel window, press 'Alt+F8' to select the macro and then hit the 'Run' button.

References
For more information about the properties and Excel constants used inside the above macros, type the following text on a module sheet:
  • Range.Characters
  • IsNumeric
  • IsNull
  • Len
  • IsEmpty
  • LCase, UCase
  • Left, Right
Highlight the property or constant about which you want more information, and then press F1.

Supported versions of MS Excel:
APPLIES TO
  • Microsoft Office Excel 2010
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition


Anonymous said...

Hi,

I was trying out the Toggle Case macro and it is running fine except it is not giving or storing the changed output anywhere.

Please help.