There are 2 built-in functions in Excel for converting text to either UPPER CASE or Proper Case.
The 2 functions are shown below :
=UPPER(A1)
=PROPER(A1)
Note - Proper Case means the first letter of every word in string is capital or uppercase.
The Excel macro code below can be used to change existing text to either UPPER CASE or Proper Case.
Code:
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, "website.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
Bookmarks