try this function changed
Function ProperCase_r( _
ByVal s As String) As String
'__________________________________________
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
'https://sites.google.com/site/e90e50/vbscript/regexp
'__________________________________________
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Dim v, i As Long, x
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
s = VBA.StrConv(s, vbProperCase)
v = Split("an|a|the|or|and|on|over|abore|" & _
"under|blow|between|near|beside|among", "|")
For Each x In v
re.Pattern = "\b" & VBA.StrConv(x, vbProperCase) & "\b"
s = re.Replace(s, x)
Next
Debug.Print s
re.Pattern = _
"(^|[.!?(]\b|[.!?(]\s+\b|\r\b|\f\b|\n\b)(.)"
ProperCase_r = _
re.Replace(s, "$1§$2§")
v = Split(ProperCase_r, _
"§", , vbTextCompare)
For i = 1 To UBound(v) Step 2
v(i) = VBA.StrConv(v(i), vbUpperCase)
Next
ProperCase_r = Join(v, "")
End Function
regards
r
2010/11/13 amrahs k <amrahs...@gmail.com>
> Hi,
>
> First I would like to say thank to you. The script that you had given to me
> works fine though I have a constraint as-
>
> The below code is does not handle "(" in the input. Please look into the
> below example.
>
> *Input: CASE MANAGEMENT CONFERENCE QUESTIONNAIRE (LOCAL RULE 12.7)*
> *Output: Case Management Conference Questionnaire (local Rule 12.7)*
>
> It does not handle the open paranthesis "(". As a result the word "LOCAL"
> has been converted in "local".
>
> Kindly look into this and help me to solve this.
>
>
> Function ProperCase_r( _
> ByVal s As String) As String
> '__________________________________________
> '¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
> 'https://sites.google.com/site/e90e50/vbscript/regexp
> '__________________________________________
> '¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
> Dim v, i As Long, x
> Dim re As Object
> Set re = CreateObject("vbscript.regexp")
> re.Global = True
>
> s = VBA.StrConv(s, vbProperCase)
> v = Split("an|a|the|or|and|on|over|abore|" & _
> "under|blow|between|near|beside|among", "|")
>
> For Each x In v
> re.Pattern = "\b" & VBA.StrConv(x, vbProperCase) & "\b"
> s = re.Replace(s, x)
> Next
> Debug.Print s
> re.Pattern = _
> "(^|[.!?]\b|[.!?]\s+\b|\r\b|\f\b|\n\b)(.)"
>
> ProperCase_r = _
> re.Replace(s, "$1§$2§")
> v = Split(ProperCase_r, _
> "§", , vbTextCompare)
> For i = 1 To UBound(v) Step 2
> v(i) = VBA.StrConv(v(i), vbUpperCase)
> Next
> ProperCase_r = Join(v, "")
> End Function
> Sub test()
> Dim s As String
> s = "AGREEMENT. THE ADOPTION THE ADULT OR MARRIED MINOR "
> MsgBox ProperCase_r(s)
> End Sub
>
> *Thanks,*
> *Sharma*
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
