# 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

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