Changing capitalization in cells.

J

JMax

I have multiple columns of data entered by users. Some entered thei
responses all in CAPS, some all lower case, and every combination i
between. Is there anyway to format the actual letters so they are al
uniform? I do not care if it is in all caps or lower case, I jus
would like all of the data the same way.

Thanks
 
R

Ron de Bruin

Hi JMax

Check out 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
 
R

Rollin_Again

Lets assume Column A contains your data. If cell A1 contains the firs
value, place any of the following functions into cell B1 and copy th
formula down the remainder of the column. If you have lots of data t
convert I recommend using one of the macros that Ron has mentione
above.


=UPPER(A1)

= LOWER(A1)

= PROPER(A1)






Rolli
 
J

JMax

Thank you both for your reccomendations, I am novice when it comes t
VBA so I like to look at as much code as possible to relate to th
logic but the formulas were the quickest solution.

Thanks again,
JMa
 
Top