Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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"? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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"? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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"? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct bllank function handling | Excel Worksheet Functions | |||
PROPER FUNCTION | Excel Worksheet Functions | |||
checkbox event handling function | Excel Worksheet Functions | |||
PROPER function | Excel Worksheet Functions | |||
How do I use the PROPER function? | Excel Worksheet Functions |