Changing lower to upper case

[Louise]

Good morning all

Can anybody tell me if there is a quicker way to change
upper case to lower case in Excel? I know you can use
the =upper command but this puts the text in a different
cell, not where the original lower case text was.

Is there a quick method that I don't know about? It's
driving me insane...........

Thank you.

Louise

 

[Ken Wright]

Lower Case or proper Case??

http://www.cpearson.com/excel/case.htm
or
http://www.mvps.org/dmcritchie/excel/proper.htm

Some misc examples of case macros from various people in the group:-

Sub CAPS()
'select range and run this to change to all CAPS
Dim Cel As Range
For Each Cel In Intersect(Selection, ActiveSheet.UsedRange)
Cel.Formula = UCase$(Cel.Formula)
Next
End Sub

-------------------------------------------------------

Sub MakeUpperCase()
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula = False Then
cell.Value = UCase(cell.Value)
End If
Next cell
End Sub
Sub MakeLowercase()
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula = False Then
cell.Value = LCase(cell.Value)
End If
Next cell
End Sub


--------------------------------------------------------

Sub MakeProperCase()
Application.ScreenUpdating = False

Dim myCell As Range
Dim myRng As Range

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells _
.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a range that contains text--no formulas!"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Value = StrConv(myCell.Value, vbProperCase)
Next myCell

Application.ScreenUpdating = True
End Sub


--------------------------------------------------------

Sub ProperCaseMak()
Dim c As Range
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
If c.HasFormula = False Then
c.Value = StrConv(c.Value, vbProperCase)
End If
Next c
Application.ScreenUpdating = True
End Sub

--------------------------------------------------------

Sub MakeUpperCase2()
Application.ScreenUpdating = False

Dim myCell As Range
Dim myRng As Range

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells _
.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a range that contains text--no formulas!"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Value = StrConv(myCell.Value, vbUpperCase)
Next myCell

Application.ScreenUpdating = True
End Sub

--------------------------------------------------------

Sub ToggleCase2()
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula = False And cell.Value = UCase(cell.Value) Then
cell.Value = LCase(cell.Value)
Else
cell.Value = UCase(cell.Value)
End If
Next cell
End Sub

 

[Louise]

Thanks for your replies. And here's me thinking there
was an easy way! Didn't realise it would have to involve
macros!!

 

[Ken Wright]

LOL - Running one of these is as simple as simple can get, honest.

If you like, I can send you a small workbook with all these in it, and then all you have to do is
open that workbook and your own workbook. Then whilst in your workbook, simply do Tools / Macro /
Macros / 'Pick one from the list' - That's it.

 

[Ken Wright]

Just mailed it to you.

 

[David McRitchie]

Hi Shailesh,
I think the round robin approach will not fix the data as you are taking
whatever is found in a cell and making it different. I think frequently
someone is going to have a mixture of upper, title, lower case, and
that approach definitely would not work in trying to get everything into
a consistent appearance. From the looks of your
web page your addin looks like a better choice than your posted
macro based only on the fact that there is a choice in it as to type of
output, and not from looking at code..

Hi Louise,
It sounds like you don't know how to
install a macro, in which case the following may help
Getting Started with Macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hi Louise and Ken,
I think you will find the macro at
http://www.mvps.org/dmcritchie/excel/proper.htm#upper

runs much faster than the other examples when processing a significant
number of cells in your selection, because it turns
off screen updating and calculation. I think all of the examples on the
two web pages and additional examples cut down the selection
to the used range. That is an important consideration as it could take
several minutes to process each cell in a single column without such
limitation, that is important.

Each line of code is described on the page (see #notations and #recap)