Change case in database fields from CAPS to Proper

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Use the function called PROPER
ie PROPER(A1) turns BILL SMITH to Bill Smith

Rob Coombs said:
We are trying to edit 1000 names in excel, all entered as CAPITALS, and we
Click to expand...
would like to convert their name, address and city to Proper case and
haven't had any luck yet.
 
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
----------------------------------------------------------------------------



Rob Coombs said:
We are trying to edit 1000 names in excel, all entered as CAPITALS, and we
Click to expand...
would like to convert their name, address and city to Proper case and haven't
had any luck yet.
 
Back
Top