Proper() function handling of apostrophe

[Valpey]

The Proper() function incorrectly handles possessive apostrophes. (e.g.
Proper(STEVE'S BAR AND GRILL) returns "Steve'S Bar And Grill". Is there a
simple way to return "Steve's Bar And Grill"; or even better "Steve's Bar and
Grill"?

 

[Fred Smith]

Not with Proper. That's the way it works. You could develop something with
Subsitute, as in:

=SUBSTITUTE(SUBSTITUTE(PROPER("STEVE'S BAR AND
GRILL"),"'S","'s"),"And","and")

But I suspect it would get to unwieldy to handle all situations.

By the way, Proper is designed for proper names, like O'Leary. That's why is
capitalizes after an apostrophe.

Regards,
Fred.

 

[ryguy7272]

Click on all the problematic cells, and run this sub:
Sub ChangeCase()
Application.ScreenUpdating = False
Dim r As Range
nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for
lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select
Case Desired"))
Select Case nCase
Case "L"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = LCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = LCase(r.Value)
End If
Next

Case "U"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = UCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = UCase(r.Value)
End If
Next
Case "P"

For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = Application.Proper(r.Formula)
'R.Formula = R.Value
Else
r.Value = StrConv(r.Value, vbProperCase)
End If
Next
End Select
Application.ScreenUpdating = True
End Sub

PS, I didn't come up with this idea; found it on the Excel-Programming DG a
while back...

Regards,
Ryan---