lowercase to uppercase
 

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

Thank you.

Louise

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)


"Louise" wrote in message
...
What is the easiest way to change text in Excel from upper to lower case
or
vice versa?

Thank you.

Louise

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"

"Louise" wrote:

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

Thank you.

Louise

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


--
Don Guillett
SalesAid Software

"Louise" wrote in message
...
What is the easiest way to change text in Excel from upper to lower case
or
vice versa?

Thank you.

Louise

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


On Mon, 10 Jan 2005 08:09:49 -0600, "Don Guillett" wrote:

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

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

On Mon, 10 Jan 2005 10:45:18 -0000, "Bob Phillips"
wrote:

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

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

On Mon, 10 Jan 2005 13:10:35 -0600, Myrna Larson
wrote:

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


On Mon, 10 Jan 2005 08:09:49 -0600, "Don Guillett" wrote:

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