Looking for a Better Find & Replace Solution

[JeffGrant]

Hi All,

I would like to find a better way to do this Find & Replace.
In my model, I have a data range which is constantly being appended. The data being added is being downloaded from an API.
In the Find and Replace, I have many instances of:
Find "Hello", Replace with "Hello World". All good there on the first pass.
However, on the second pass with the appended data, when doing the same Find & Replace, I regularly end up with "Hello World World"
To get around this, further down the Find & Replace list, I Find "World World" & Replace with "World".
Because of the timing of the API downloads, the Find and Replace code does not always run, which means I end up with "Hello World" & "Hello World World" in a Filtered list.

Has anybody got as better idea how I can handle this and only end up with "Hello World" in the Filtered list?

Thanks in advance for all your great ideas.

 

[Georgiboy]

If you are doing this with VBA then it could be that you need to set the 'LookAt' part of the replace code to 'xlWhole'. You may have it set to 'xlPart' or not have it set at all. If you don't set that part in the code then VBA will default to the last state used, be it manual or code. Example below:

Sub test()
    Columns("A:A").Replace What:="Hello", Replacement:="Hello World", LookAt:=xlPart
    Columns("A:A").Replace What:="Hello", Replacement:="Hello World", LookAt:=xlWhole
End Sub

If you are doing this manually then check the 'Match entire cell contents' box in the find/ replace box.

 

[JeffGrant]

Hi Georgiboy,

thank you for that. Your reply sent me on a whirl wind tour of Youtube videos. My code, which I have been using for several years, was actually based upon the Substitute function and some very inefficient arrays for doing this task.

Your reply forced me to rewrite that part of the code. It is now much faster, more efficient and far better structured.

Thanks again