Changing cases to proper

[Guest]

I was sent an Excel spreadsheet with names and addresses, it's all in upper
case and I want to change the entire document to "proper" case. Please help!

Ps. 861 names w/ 7 columns

 

[Fred Smith]

Insert a helper column. Enter the formula =proper(a1), and copy down. Copy the
new column, and Paste Special>Values the old column. Delete the helper column.
Repeat for each column.

You will probably have to manually correct some names, especially last names
beginning with Mc and Mac.

 

[Guest]

I inserted a new column (a), I highlight the column and I enter =proper (a1)
and the a1 cell comes up w/ an "o", what am I doing wrong here?

 

[Gord Dibben]

To do all at once without 7 columns of formulas would require VBA.

See David McRitchie's site for code.

http://www.mvps.org/dmcritchie/excel/proper.htm#proper

Note the various sets of code, one of which looks after the Mac's and Mc's and
other stuff.


Gord Dibben MS Excel MVP

 

[Guest]

Thanks Gord, but that's way too complicated for me. I think I'd rather just
manually change the few complex names and use an easier method.

 

[aidan.heritage]

Thanks Gord, but that's way too complicated for me. I think I'd rather just
manually change the few complex names and use an easier method.








- Show quoted text -

Actually, the VBA for this is VERY easy

Sub changer()
Dim cell
For Each cell In ActiveSheet.UsedRange
cell.Value = WorksheetFunction.Proper(cell.Value)
Next
End Sub

 

[Guest]

Hi Dennis,
Your referencing the first cell in your helper column change the formula to
=PROPER(B1) where B1 is the first cell of your names.

Hope this helps,

Gav.

 

[Guest]

Usually i wouldn't suggest this but i do it myself from time to time, copy
your rows, paste them into word, select them all and select Format->Change
Case->Title Case then copy and paste back into excel.

 

[Gord Dibben]

This could be dangerous code.

There may be formulas in the usedrange.

These will all get changed to values only.

Amend to this

Sub changer()
Dim cell
For Each cell In ActiveSheet.UsedRange
cell.Formula = WorksheetFunction.Proper(cell.Formula)
Next
End Sub


Gord Dibben MS Excel MVP

 

[politesse]

I think a macro would be good if it only changed the selected cell of cells,
but not if they had formulas

At least it would be good for me

 

[Gord Dibben]

Are you asking for a macro?

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

 

[politesse]

Thanks Gord that works great

Are you asking for a macro?

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

 

[Gord Dibben]

And thanks to David also.


Gord

Thanks Gord that works great