lowercase to uppercase

[Louise]

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)


"Louise" <[email protected]> wrote in message
news:[email protected]...
> What is the easiest way to change text in Excel from upper to lower case
or
> vice versa?
>
> Thank you.
>
> Louise

[Defoes Right Boot]

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

[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


--
Don Guillett
SalesAid Software
[email protected]
"Louise" <[email protected]> wrote in message
news:[email protected]...
> What is the easiest way to change text in Excel from upper to lower case
or
> vice versa?
>
> Thank you.
>
> Louise

[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


On Mon, 10 Jan 2005 08:09:49 -0600, "Don Guillett" <[email protected]> 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

[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

On Mon, 10 Jan 2005 10:45:18 -0000, "Bob Phillips"
<[email protected]> 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

[Myrna Larson]

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

On Mon, 10 Jan 2005 13:10:35 -0600, Myrna Larson
<[email protected]> 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" <[email protected]> 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