Replace "comma" with "and" in line of text

audioman · #1 01-12-2016, 01:43 PM

Need a macro which will scan a word document and find each instance of "apples, oranges, peaches" then replace with "apples, oranges and peaches" and/or find "apples, oranges" then replace with "apples and oranges"

Robert2 · #2 01-12-2016, 04:32 PM

You can do it with a wildcard Find/Replace.
Find What : (apples), (oranges), (peaches)
Replace With: \1 and \2 and \3

macropod · #3 01-13-2016, 03:01 AM

Robert:
Audioman probably wants to change strings like:
"apples, oranges, peaches"
to:
"apples, oranges and peaches"
not merely the literal "apples, oranges, peaches" string to:
"apples and oranges and peaches"
and to change other instances like:
"apples, oranges"
to:
"apples and oranges"
but your Find/Replace code won't do any of that.

It is also quite likely there will be many other commas that shouldn't be replaced - as in the one used in this sentence.

Audioman:
Although a macro could be written, the level of interactivity required would scarcely make it any more efficient than using a non-macro Find/Replace where you selectively choose whether to replace a given comma with ' and'.

audioman · #4 01-13-2016, 06:02 AM

You're right macropod. I was using apples, oranges, peaches as an example. The document will actually contain people's first names and, of course, the names will vary. Someone gave me a macro a couple of years ago and it worked perfectly. Unfortunately, shame on me, I need it again and I can't find it.

macropod · #5 01-13-2016, 07:43 PM

Try:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim Rng As Range
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "<[A-Z][! ]@, [A-Z][! ]@>"
    .Replacement.Text = ""
    .Forward = False
    .Wrap = wdFindStop
    .Format = False
    .MatchWildcards = True
    .Execute
  End With
  Do While .Find.Found
    If Len(.Text) > 1 Then
      Set Rng = .Duplicate
      .MoveEndUntil ",", wdBackward
      .Start = .End - 1
      .Text = " and"
      With Rng
        .Collapse wdCollapseStart
        Do While .Characters.First.Previous.Previous = ","
          .Start = .Start - 3
          .Start = .Words.First.Start
          If Not .Characters.First Like "[A-Z]" Then
            .Collapse wdCollapseEnd
            Exit Do
          End If
        Loop
      End With
      .Start = Rng.Start
      .Collapse wdCollapseStart
      .Find.Execute
    Else
      Exit Do
    End If
  Loop
End With
Application.ScreenUpdating = True
End Sub

audioman · #6 01-21-2016, 02:01 PM

Hi macropod:
I found the code you provided back in 2014. It's:
Find = ,([!,^13^l]@[^13^l])
Replace = ^32and\1

It worked quite well at that time. I'm now trying to incorporate the code in a macro which will find and replace multiple instances of the name string. I was able to do that in 2014.
Unfortunately, probably due to changes in VBA, I keep getting errors when trying to run this code.
Can you help?

macropod · #7 01-21-2016, 02:09 PM

That's not VBA code. As indicated in the 2014 post (https://www.msofficeforums.com/word-...html#post61045) it's a wildcard Find/Replace expression. Even then, I clearly said there were limitations on what such an expression could achieve. The macro in post #5 is far more sophisticated, employing not only a (different) wildcard Find but also logic to ensure the 'and' only gets inserted after the last Proper name in a series.

audioman · #8 01-21-2016, 03:11 PM

Thanks macropod, I can make that code work!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Error: "Changes made were lost...reconnect with server", when switching "from" field	randhurrle	Outlook	2	02-25-2015 06:51 PM
remove repeated words with " macro " or " wild cards " in texts with parentheses and commas	jocke321	Word VBA	2	12-10-2014 11:27 AM
Suppress comma in "city , stat zip" line when blank	DHammer	Mail Merge	1	05-30-2014 02:43 AM
How to edit the "Format" and the "show level" of an EXISTING table of content?	Jamal NUMAN	Word	2	08-14-2011 10:46 AM
How to choose a "List" for certain "Heading" from "Modify" tool?	Jamal NUMAN	Word	2	07-03-2011 03:11 AM