Proper Function

[Guest]

Hi,

I have about 20 columns in an Excel spreadsheet that
contain name, address, etc. in various forms of
capitalization. I believe I can use the proper fucntion
to capitalize the first letter of each work and make all
other letters lowercase.

My question is how do I do this for a series of columns,
or selected columns all at one time. I see to be getting
an error message related to a circular pattern or
something like that

Any help is much appreciated

Thanks
Bill

 

[theillknight]

If Columns A through T are the columns you want to change, then you nee
U1 to say:

=proper(a1)

Drag that column to the right for the remaining 19 columns and howeve
far down the columns are. Then, to change your output from a formul
to text, highlight all the formula columns (the ones with 'proper')
copy, paste special - values. Now you should be able to move the
freely

 

[Gord Dibben]

To do 20 columns at a whack you need VBA.

If unsure what to do with this code, see David McRitchie's site for getting
started with VBA.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Sub Proper_Casek()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = Application.Proper(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Sub optProper_Click()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = Application.Proper(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP