Replace multiple text in Range

Question

I want to replace multiple text / punctuation marks for example

"," with " "

"'s" with " "

, 's are Extra text which I do not need for next step.

Replace method can change only one time

Is there any other way to replace multiple text from the following sentence?

"Aabar remains focused on Abu Dhabi’s plans expansion, In Dubai ahead of Expo 2020"

Replace works with , but 's is still there.

Sub make_range_replace_string()
Dim R As Range
Dim F As String
Do
    selection.Find.ClearFormatting
    selection.Find.Font.Bold = True
    With selection.Find
        .Forward = True
        .Wrap = wdFindStop
    End With
    selection.Find.Execute
    If selection.Find.Found Then
   Set R = ActiveDocument.Range(selection.Range.Start, selection.Range.End)

   F = Replace(R, ",", "") 

   MsgBox F

Else
Exit Do
End If
Loop

End Sub

You can nest the Replace functions like F = Replace(Replace(R, ",", "") ,"'s", " "). — Doug Glancy
– Doug Glancy, Commented May 15, 2016 at 14:43
@DougGlancy Replace works with , but 's is still there. what should I do? — VBAbyMBA
– VBAbyMBA, Commented May 15, 2016 at 14:56
Create a two-dimensional array with all the replacement-words and iterate through the list with the above posted code. — Ralph
– Ralph, Commented May 15, 2016 at 15:33

Thomas Otto · Accepted Answer · 2016-05-18 19:39:08Z

2

Doug Glancey is correct, just use ’ instead of ' though. In other words; use the Replace-function multiple times.

Dim F As String

F = "Aabar remains focused on Abu Dhabi’s plans expansion, In Dubai ahead of Expo 2020"
F = Replace(F, ",", " ")
F = Replace(F, "`s", " ")
F = Replace(F, "’s", " ")
F = Replace(F, "'s", " ")
F = Replace(F, Chr(39) & "s", " ")
F = Replace(F, Chr(96) & "s", " ")

MsgBox F

edited May 18, 2016 at 19:39

answered May 15, 2016 at 15:48

Thomas Otto

494 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

VBAbyMBA Over a year ago

as you notice 's is attached to the text, so Replace(F, "’s", " ") is not working. There should be another way?

Thomas Otto Over a year ago

Strange, I've edited the answer with more variations of the 's

Collectives™ on Stack Overflow

Replace multiple text in Range

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related