How do I change Letter case (lower to Upper) in a spreadsheet???

M

mineisjosh

Please Help.... I cannot figure out how to change the text from lower to
upper case. I have tried working with the formula suggested by MS but I
cannot get it to work. I am sure I am missing something simple, any help is
appreciated. Thanks.
 
D

Don Guillett

the formula would refer to another cell not the one you are in
=upper(a1)
to change the cell you are in you need a macro. Maybe this will help.

Sub ChangeCase() 'Don Guillett
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
 
I

IanRoy

Hi mineisjosh;
Here is an example: Put mineisjosh in cell A1. Put the formula =UPPER(A1) in
cell B1. Enter. MINEISJOSH will appear in cell B2. Copy cell B2, and Paste
Special> Values into A1. Clear cell B2.
Regards,
Ian.
 
G

Gord Dibben

Well, not quite....<g>

In B1 enter =UPPER(A1) to return MINEISJOSH is B1.

Copy/paste special>values of B1 to A1 then delete B1


Gord Dibben Excel MVP
 
I

IanRoy

Oops.

Gord Dibben said:
Well, not quite....<g>

In B1 enter =UPPER(A1) to return MINEISJOSH is B1.

Copy/paste special>values of B1 to A1 then delete B1


Gord Dibben Excel MVP
Click to expand...
 
Top