Change the Case of Text

  • Thread starter Thread starter Felix
  • Start date Start date
F

Felix

Does anybody has a better and faster procedure than
Microsoft to change the case of a text to either UPPER,
LOWER or PROPER? If I format the cell as Text and apply
the formula =UPPER(A1)the cell will show =UPPER(A1). If I
change the cell format to General, then I get a Circular
Reference. In any event I can't show the name in Upper or
Lower case letters. I will appreciate any help you can
provide. Thanks
 
You need a macro Felix

See the following sites

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
 
Back
Top