Convert text to uppercase/lowercase using Macros
hello friends,
I have some worksheets and other documents in which I want to convert the lowercase letters to uppercase. And also in some documents, I want to convert the capital letters to lowercase ones. Is it possible for me to do this using macros ?
kindly help....
Re: Convert text to uppercase/lowercase using Macros
Change Text to Uppercase
The following macro converts the text in cells to uppercase -
Code:
Sub Uppercase()
'
'This will make the text of any selection of cells uppercase.
'
For Each x In Selection
x.Value = UCase(x.Value)
Next
End Sub
Note - This macro works only for the selected cells within Excel and it will work for ranges that include rows, columns, and both at the same time.
Re: Convert text to uppercase/lowercase using Macros
Change Text to Lowercase
The following macro converts the text in cells to lowercase -
Code:
Sub Lowercase()
'
'This will make the text of any selection of cells lowercase.
'
For Each x In Selection
x.Value = LCase(x.Value)
Next
End Sub
Note - This macro works only for the selected cells within Excel and it will work for ranges that include rows, columns, and both at the same time.
Re: Convert text to uppercase/lowercase using Macros
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