Changing text case in entire database column from JOHN to John

[Guest]

I need to change Upper text to Upper and lower text , example JOHN to John,
in entire column. First and last names are in seperate columns typed in excel
spreadsheet. This is for a mail merge.

 

[Guest]

Look at PROPER function:

In helper column(s) put:

=PROPER(A2) assuming data in column A and copy down as required.

Then Copy & Paste Special=>Values (the latter to either helper or original
column) and delete redundant column(s)

HTH

 

[Gord Dibben]

You can use the PROPER function.

Assume first names in Column A and last in Column B.

In C1 enter =PROPER(A1)

In D1 enter =PROPER(B1)

Selecy C1 and D1 then double-click on fill handle of D1.

When happy with results, copy columns C and D the, in place, Paste
Special>Values>OK>Esc.

Delete the original A and B columns.

OR you could use VBA to change all at once in place with no need for formulas.

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 MS Excel MVP


On Tue, 10 Oct 2006 10:18:01 -0700, Kimberly in Palm Springs <Kimberly in Palm