Converting all caps to regular case

  • Thread starter Thread starter Louis Bernstein
  • Start date Start date
L

Louis Bernstein

I have a large spreadshet that has a column of names.

Some are all upper case like JOHN SMITH and some are
normal, like John Smith.

Would you know how to do a global format to change ALL
NAMES to All Names (proper case)?

Thank you.
 
See this webpages Louis

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

Here are some Macro's for 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
 
Hi Ron,
Are you suggesting that de Bruin be De Bruin
if you know the exceptions they should be included the
macro. Hopefully the source of all capital names will
eventually be corrected. In the meantime I think the
only valid answer of your suggestions is on my site:
http://www.mvps.org/dmcritchie/excel/proper.htm

Exceptions must be coded in the macro because there
are differences in the way people want to see their names
even if the letters are the same, you have to customize
based on your own experience or from what you are told.



Ron de Bruin said:
See this webpages Louis [clipped]
Here are some Macro's for the selection [clipped]

Louis Bernstein said:
I have a large spreadshet that has a column of names.

Some are all upper case like JOHN SMITH and some are
normal, like John Smith.

Would you know how to do a global format to change ALL
NAMES to All Names (proper case)?
Click to expand...
Click to expand...
 
See this webpages Louis

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



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




David McRitchie said:
Hi Ron,
Are you suggesting that de Bruin be De Bruin
if you know the exceptions they should be included the
macro. Hopefully the source of all capital names will
eventually be corrected. In the meantime I think the
only valid answer of your suggestions is on my site:
http://www.mvps.org/dmcritchie/excel/proper.htm

Exceptions must be coded in the macro because there
are differences in the way people want to see their names
even if the letters are the same, you have to customize
based on your own experience or from what you are told.



Ron de Bruin said:
See this webpages Louis [clipped]
Here are some Macro's for the selection [clipped]

Louis Bernstein said:
I have a large spreadshet that has a column of names.

Some are all upper case like JOHN SMITH and some are
normal, like John Smith.

Would you know how to do a global format to change ALL
NAMES to All Names (proper case)?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top