how do i change a full excel sheet to proper after it has been ty.

[Guest]

how do i change a full excel sheet to proper after it has been typed
see i have typed up a spreed sheet but as i have realized the first string
is still in lower case is there a way to change the whole work sheet at once

 

[Guest]

Depends what you mean by a 'whole' worksheet.

To change a column of text values to proper, insert a blank column adjacent
to the one iwth the text values. Say the text values are in A, and you've
inserted a new, empty B column

Use this formula in B1 and copy it down as needed

=PROPER(A1)

Now select all the formula values and Edit->Copy
Select all the original values in A and Edit->Paste Special->Values
Delete column B

 

[Gord Dibben]

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