Convert text to uppercase/lowercase using Macros

[Anas]

Sponsored Links

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

[JonathanD]

Change Text to Uppercase

The following macro converts the text in cells to uppercase -

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.

[JonathanD]

Change Text to Lowercase

The following macro converts the text in cells to lowercase -

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.

[Raine]

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.

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