Removing Exact Text String from giving Excel Cells - Unexpected Behaviour

Hi,
I'm using this Macro to remove predefined text values from selected Excel cells which contain multiple text values separated by commas. I'm targeting mostly any single letter words or prepositions. (a, on, to...etc) Sometimes these single letters come at the beginning of a cell (no space before), so I had to set the code to remove "a, " or "on, ". Doing so, when I run the code they get removed whether they come as single words or parts of other words.

Example: Running the Macro over cells containing:
A2: a, ticket, to, paris
A3: propaganda, multiple, offers
A4: on, mention, on, movies

It produces:
A2: ticket, paris
A3: propagandmultiple, offers
A4: mentimovies

The Code:

Dim cell As Range, MyStr As String

Application.ScreenUpdating = False
For Each cell In Selection
MyStr = cell.Value
MyStr = Replace(MyStr, "to, ", "")
MyStr = Replace(MyStr, " to, ", "")
MyStr = Replace(MyStr, "a, ", "")
MyStr = Replace(MyStr, " a, ", "")
MyStr = Replace(MyStr, "on, ", "")
MyStr = Replace(MyStr, " on, ", "")
cell.Value = MyStr
Next cell
Application.ScreenUpdating = True
End Sub


Any suggestions to get that working properly?

many thanks

Will keep that in mind.
For the second time, thanks a lot Tigeravatar. I do appreciate your solutions

Originally Posted by tigeravatar

ghoneim,

In the future, please wrap your code in code tags, link in my sig for how.
As for your question, something like this should work for you:

Please Login or Register  to view this content.