---

Change Text to Upper Case or Proper Case

Change Text to Upper Case or Proper Case. Convert Text to Proper Case or Upper Case

 

Excel has 2 built in functions for converting text to either UPPER CASE or Proper Case. The 2 functions that do this are shown below;

=UPPER(A1)
=PROPER(A1)

These Excel functions work well when referring to cells that house the text. However, there are many instances when using the Worksheet Function approach is not practical. The Excel macro code below can be used to change existing text to either UPPER CASE or Proper Case. If you run the macro with only a single cell selected it will work on the entire Worksheet. If you run the macro with more than 1 cell selected it will work on only your selection. The other settings that the StrConv Function take are shown below. See the Excel VBA help for specifics.

vbUpperCase = Converts the string to uppercase characters.
vbLowerCase = Converts the string to lowercase characters.
vbProperCase = Converts the first letter of every word in string to uppercase.
vbWide = Converts narrow (single-byte) characters in string to wide (double-byte) characters.
vbNarrow = Converts wide (double-byte) characters in string to narrow (single-byte) characters.
vbKatakana = Converts Hiragana characters in string to Katakana characters.
vbHiragana = Converts Katakana characters in string to Hiragana characters.
vbUnicode = Converts the string to Unicode using the default code page of the system. (Not available on the Macintosh.)
vbFromUnicode = Converts the string from Unicode to the default code page of the system. (Not available on the Macintosh.)

Sub ConvertCase()

Dim rAcells As Range, rLoopCells As Range

Dim lReply As Long



    'Set variable to needed cells

    If Selection.Cells.Count = 1 Then

        Set rAcells = ActiveSheet.UsedRange

    Else

       Set rAcells = Selection

    End If





    On Error Resume Next 'In case of NO text constants.

    'Set variable to all text constants

    Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)

    

    If rAcells Is Nothing Then

       MsgBox "Could not find any text."

       On Error GoTo 0

       Exit Sub

    End If

          

    lReply = MsgBox("Select 'Yes' for UPPER CASE or 'No' for Proper Case.", _

    vbYesNoCancel, "OzGrid.com")

    If lReply = vbCancel Then Exit Sub

  

    If lReply = vbYes Then ' Convert to Upper Case

          For Each rLoopCells In rAcells

              rLoopCells = StrConv(rLoopCells, vbUpperCase)

          Next rLoopCells

    Else ' Convert to Proper Case

          For Each rLoopCells In rAcells

              rLoopCells = StrConv(rLoopCells, vbProperCase)

          Next rLoopCells

    End If

    

End Sub

See also:

Color or Format a Formula Referenced Cells - Precedents

Copy Multiple Column & Row Records Into Single Row Records

Add Excel Worksheets in Numeric Sequence

Add Worksheets to Excel via VBA

 

Free Training Course: Lesson 1 - Excel Fundamentals

 

See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

Gallery

---

• 
• 
• 
• 
• 
•