Change case in database fields from CAPS to Proper

[Rob Coombs]

We are trying to edit 1000 names in excel, all entered as CAPITALS, and we would like to convert their name, address and city to Proper case and haven't had any luck yet.
Any recommendations?

Thank you in advance for your time and assistance.

 

[Dave R.]

Use the function called PROPER
ie PROPER(A1) turns BILL SMITH to Bill Smith

We are trying to edit 1000 names in excel, all entered as CAPITALS, and we

would like to convert their name, address and city to Proper case and
haven't had any luck yet.

 

[Ken Wright]

Always back your data up first - A couple of routines to do as you asked.

----------------------------------------------------
Sub MakeProperCase()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim myCell As Range
Dim myRng As Range

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells _
.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a range that contains text--no formulas!"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Value = StrConv(myCell.Value, vbProperCase)
Next myCell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


-------------------------------------------------
Sub ProperCaseMak()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim c As Range
For Each c In ActiveSheet.UsedRange
If c.HasFormula = False Then
c.Value = StrConv(c.Value, vbProperCase)
End If
Next c
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

We are trying to edit 1000 names in excel, all entered as CAPITALS, and we

would like to convert their name, address and city to Proper case and haven't
had any luck yet.