Change Text Case To Upper, Proper or Lower

  • <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN"><html><head><meta http-equiv="Content-Language" content="en-us"><title>Convert Excel Text To Upper Case</title><meta name="description" content="Convert Excel Text To Upper Case, Lower Case or Proper Case"><meta name="keywords" content="Convert Excel Text To Upper Case, Lower Case or Proper Case"><link rel="STYLESHEET" href="http://www.ozgrid.com/css/ozgrid.css" type="text/css"><script type="text/javascript" src="http://www.ozgrid.com/SideNavJS/stmenu.js"></script>
    </head><body><div align="center"></div><h1>Convert Excel Text To Upper Case</h1><h2>EXCEL VBA: Changing text case</h2><p class="j">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;</p>
    <pre>
    <b>Sub UPPERCASE()</b>
    Dim lChr As Long


    With Selection
    For lChr = 97 To 122
    .Replace Chr(lChr), UCase(Chr(lChr))
    Next lChr
    End With
    <b>End Sub</b></pre><p class="j">If you are wondering how this works, let me explain. Chr(97) in VBA results in a lower case &quot;a&quot;. Chr(98)=&quot;b&quot;, chr(99)=&quot;c&quot; and so on....The Ucase Function is used to convert all lower case letters to UPPER CASE. &quot;a&quot; becomes &quot;A&quot; and so on...</p><p class="j">For all other cases, we can use the StrConv Function, which can convert according to the table below;</p><p class="j"><b>vbUpperCase</b> = Converts the string to uppercase characters.<br><b>vbLowerCase</b> = Converts the string to lowercase characters.<br><b>vbProperCase</b> = Converts the first letter of every word in string to uppercase.<br><b>vbWide</b> = Converts narrow (single-byte) characters in string to wide (double-byte) characters.<br><b>vbNarrow</b> = Converts wide (double-byte) characters in string to narrow (single-byte) characters.<br><b>vbKatakana</b> = Converts Hiragana characters in string to Katakana characters.<br><b>vbHiragana</b> = Converts Katakana characters in string to Hiragana characters.<br><b>vbUnicode</b> = Converts the string to Unicode using the default code page of the system. (Not available on the Macintosh.)<br><b>vbFromUnicode</b> = Converts the string from Unicode to the default code page of the system. (Not available on the Macintosh.)</p><p class="j">The code we can use is;</p>
    <pre><b>Sub ConvertCase()</b>
    Dim rAcells As Range, rLoopCells As Range
    Dim lReply As Long


    <font color="#008000">'Set variable to needed cells</font>
    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.
    <font color="#008000">'Set variable to all text constants</font>
    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 <font color="#008000">' Convert to lower case</font>
    For Each rLoopCells In rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)
    rLoopCells = StrConv(rLoopCells, vbLowerCase)
    Next rLoopCells
    Else <font color="#008000">' Convert to Proper Case</font>
    For Each rLoopCells In rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)
    rLoopCells = StrConv(rLoopCells, vbProperCase)
    Next rLoopCells
    End If


    <b>End Sub</b></pre></body></html>