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

---