Can you change letter case without having to retype words?

[Guest]

I have an excel worksheet which I need to change all the columns from
capitals to lower case text. Is there a way to do this without having to
retype the whole database?

 

[Anne Troy]

Sure. One easy way is to copy to Word, select and choose Format-Change case.

However, you can do this using a macro, which you might want to store in
your personal.xls file.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=69
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[Earl Kiosterud]

Elissa,

Use something like this in a helper column. Copy it down with the Fill
Handle.

=LOWER(A2)

or

= PROPER(A2)

If that looks like what you need, you can permanently convert the original
column with this: Select your helper column, Copy. Now select the first
cell of the original column, A (it must coorelate with the top cell that you
selected when you did the copy -- (same row)). Do Edit - Paste special -
Values. Now you can trash the helper column.

 

[Ron de Bruin]

Hi

You can use Code to do this
See this webpages

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

Here are some Macro's for changing text cells in the selection

Sub Uppercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = UCase(cel.Value)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Sub Lowercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = LCase(cel.Value)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Sub Propercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = StrConv(cel.Value, vbProperCase)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Guest]

Copy the following macro:

Sub Change_Case()
Dim ocell As Range
Dim Ans As String
Ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")
If Ans = "" Then Exit Sub
For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
Select Case UCase(Ans)
Case "L": ocell = LCase(ocell.Text)
Case "U": ocell = UCase(ocell.Text)
Case "S": ocell = UCase(Left(ocell.Text, 1)) & _
LCase(Right(ocell.Text, Len(ocell.Text) - 1))
Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
End Select
Next

End Sub