Hi, This works great. Thanks for your efforts and help. Though I have a small need as well.
The code gives the output through message box. Due to this I am unable to copy the value (i.e) Output. Also the code is only execute for the input "AGREEMENT. THE ADOPTION THE ADULT OR MARRIED MINOR". What I need to have is to applicable for any kind of inputs (i.e) the input value is not constant it may change as per the user requirement. Please help me to overcome this and many thanks in advance. Regards, Sharma On Wed, Oct 27, 2010 at 9:49 PM, r <robb....@gmail.com> wrote: > try this function: > > 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 > > regards > r > > > On 27 Ott, 11:25, amrahs k <amrahs...@gmail.com> wrote: > > Hi Team, > > > > I want a macro in MS word that will do the following. > > > > It will first convert the upper case into title case and then replace > every > > connecting word with lower case. > > > > *Example:* AGREEMENT. THE ADOPTION THE ADULT OR MARRIED MINOR > > > > *Required Output:* Agreement. The Adoption the Adult or Married Minor > > > > *Note:* The connecting word “THE” has been replaced by “the” however the > > macro left the first connecting word “THE” that followed by dot (.) > > > > Your help would be greatly appreciated. > > > > 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
