Remove matching strings by multiple substrings

[Losai]

How to remove or replace familiar words in string, by list of familiar stubs of that words, using VBA?
List contain ~30 stubs (substrings). With all of this I expect substitute hundreds matching words.

stackoverflow5.png

For this goal I use an array formula:

Please Login or Register  to view this content.

But this formula have one big disadvantages: Length limitation (255 characters for values)

So Glenn Kennedy suggest to code it in VBA.

Attention! Some words contain two different stubs, and this can create conflict in substitution.
Opinion! Is better to replace/substitute, instead of delete/trim because in that way we can make correction in word, not just trimming.

[XLent]

will be more succint methods but

Please Login or Register  to view this content.

above, stored in Standard Module, would be called from cell along lines of:

D2: =XTERMS(A2)

if you wanted to change exclusion terms you can send these explicitly in optional 2nd param (pipe delimited), e.g.

D2: =XTERMS(A2,"auti|mpl")
would return different results

note: UDF is case insensitive search by default (i.e. both AUTI and auti excluded) -- this can be modified if necessary by altering the InStr compare method

[jindon]

D2;
=VBAReplace(A2,"auti","tion","mpl")

Add/Remove substrings as you wish.

Please Login or Register  to view this content.

[Losai]

D2;
=VBAReplace(A2,"auti","tion","mpl")

Add/Remove substrings as you wish.

I like and i will use this.
Im glad you highlite formula missing.
Now is solved fully.
Thank you!