Hi, Thanks for the swift response. I came up with different scenario after applying the new function that you shared me. Input: DECLARATION BY LAW ENFORCEMENT FOR WALK-THROUGH ARREST WARRANT AND ORDER Output: Declaration by Law Enforcement for Walk-through Arrest Warrant and Order
Input: SETTLEMENT CONFERENCE BRIEF/MANDATORY TRIAL STATEMENT Output: Settlement Conference Brief/mandatory Trial Statement Input: “DECLARATION” FOR “COURT” ASSIGNMENT Output: “declaration” for “court” Assignment It also does not handle the following special characters */ " -* Kindly look into this and help me to overcome this issue. Thanks, Sharma On Sat, Nov 13, 2010 at 8:49 PM, roberto mensa <robb....@gmail.com> wrote: > 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 > -- ---------------------------------------------------------------------------------- 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
