Proper() function handling of apostrophe
 

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"?

Proper() function handling of apostrophe
 

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.

"Valpey" wrote in message
...
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"?

Proper() function handling of apostrophe
 

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---

--
RyGuy


"Valpey" wrote:

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"?