lowercase to uppercase

[Guest]

What is the easiest way to change text in Excel from upper to lower case or
vice versa?

Thank you.

Louise

 

[Bob Phillips]

Louise,

This macro does it

Sub ChangeCase()
Dim cell As Range
For Each cell In Selection
cell.Value = LCase(cell.Value)
Next cell
End Sub

To change to upper-case just use Ucase/

Proper case (This is. This isn't, for instance) is trickier, and you need a
worksheetfunction as well

Sub ChangeCase()
Dim cell As Range
For Each cell In Selection
cell.Value = WorksheetFunction.Proper(cell.Value)
Next cell
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

The worksheet functions UPPER and LOWER will do this.

EG if you have the text "BOB SMITH" in cell A1, then in another cell you put
=LOWER(A1) the result will be "bob smith"

Also helpful is the function PROPER - if you use this on the above example
you would get "Bob Smith"

 

[Don Guillett]

This might come in handy stored in your personal.xls file

Sub ChangeCase()
Application.ScreenUpdating = False
Dim r As Range
nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for
lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select
Case Desired"))
Select Case nCase
Case "L"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = LCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = LCase(r.Value)
End If
Next

Case "U"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = UCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = UCase(r.Value)
End If
Next
Case "P"

For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = Application.Proper(r.Formula)
'R.Formula = R.Value
Else
r.Value = StrConv(r.Value, vbProperCase)
End If
Next
End Select
Application.ScreenUpdating = True
End Sub

 

[Myrna Larson]

Hi, Don:

If a cell contains a literal (text or number), it's formula is it's value, so
you can just write

For Each r In Selection.Cells
r.Formula = LCase(r.Formula)
Next

 

[Gord Dibben]

A caveat with Bob's macro.....

If you have any formulas in the selected range, they will be wiped out and
replaced with values.

To prevent that, change to.....

Sub ChangeCase()
Dim cell As Range
For Each cell In Selection
cell.Formula = LCase(cell.Formula)
Next cell
End Sub


Gord Dibben Excel MVP

 

[Myrna Larson]

I guess I'm slipping. Those should both be "its", not "it's".