Convert a range to Proper Case

[Aussiebear]

Is there a way to convert the contents of a range to proper case without using vba?

[unmarkedhelicopter]

Yeah, ... =Proper(OldText) then Copy, Paste Values
It's not quick, it's not pretty but thare are no macros !!!

[mdmackillop]

Agreed, not without VBA, but you can fix it as you go with a worksheet event (also posted in the other thread)
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target
If .Column = 2 Then
.Value = WorksheetFunction.Proper(.Value)
.Replace "'S", "'s"
End If
End With
Application.EnableEvents = True
End Sub

[/VBA]

[Aussiebear]

And if the range already exists ( 1850 rows)?

Can a conditional format do the trick?

[mdmackillop]

Can a conditional format do the trick?

I don't see how.
You could also use a Selection Change event, and keep pressing the Down Arrow.
[VBA]Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Proper Target
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Proper Target
End Sub

Sub Proper(Target As Range)
Application.EnableEvents = False
With Target
If .Column = 2 Then
.Value = WorksheetFunction.Proper(.Value)
.Replace "'S", "'s"
End If
End With
Application.EnableEvents = True
End Sub

[/VBA]