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
