---

Convert Excel Text To Upper Case

We'll look at UPPER CASE, Proper Case and lower case. UPPER CASE is the fastest because we can use the Replace Method, like this:

Sub UPPERCASE()
Dim lChr As Long

With Selection
   For lChr = 97 To 122
      .Replace Chr(lChr), UCase(Chr(lChr))
   Next lChr
End With
End Sub

If you are wondering how this works, let me explain. Chr(97) in VBA results in a lower case "a". Chr(98)="b", chr(99)="c" and so on....The Ucase Function is used to convert all lower case letters to UPPER CASE. "a" becomes "A" and so on...

For all other cases, we can use the StrConv Function, which can convert according to the table below;

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.)

The code we can use is;

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 lower case or 'No' for Proper Case.", _
    vbYesNoCancel, "OzGrid.com")

    If lReply = vbCancel Then Exit Sub


    If lReply = vbYes Then ' Convert to lower case
          For Each rLoopCells In rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)
              rLoopCells = StrConv(rLoopCells, vbLowerCase)
          Next rLoopCells
    Else ' Convert to Proper Case
         For Each rLoopCells In rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)
              rLoopCells = StrConv(rLoopCells, vbProperCase)
          Next rLoopCells
    End If

End Sub

 

See also:

Convert a String or Number to Boolean

Convert a String to a Byte Data Type

Convert to a Currency Data Type

Convert to a Date Data Type

Convert to a Decimal Data Type

Convert to a Double Data Type

Convert to a Integer Data Type

Excel VBA: Convert to a Long Data Type

Convert to a Single Data Type

Convert to a String Data Type

Excel VBA: Convert to a Variant Data Type

 

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

---

• 
• 
• 
• 
• 
•