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

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.