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

[audioman]

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]

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

[macropod]

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]

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]

Try:

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]

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]

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]

Thanks macropod, I can make that code work!