Remove words by substring list

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

dOAb3.png

Attention! Some words contain two different stubs, and this can create conflict in substitution.
I suggest to use case insensitive functions to exclude double length of formula.
I think is better to replace/substitue, instead of delete/trim because in that way we can meke correction in word, not just trimming.

I post in Formula thread because I prefer only excel formula, without vba or power query and without helper columns.
But any suggestion is helpful for different types of users, not only for me.

Here is the workbook:

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

In your version of Excel... do you have the TEXTJOIN function? Are using a PC or a Mac?

Its better with VBA code. Unforturnatly, it is not accepted!

It can be done by formula but is made difficult 1) by so many stubs and 2) different capitalisation within stubs. VBA is probably better.

=IFERROR(TEXTJOIN(" ",,FILTERXML("<A><B>"&SUBSTITUTE(A2," ","</B><B>")&"</B></A>","//B[not(contains(., 'auti') or contains(., 'AUTI') or contains(., 'tion') or contains(., 'mpl') or contains(., 'Mpl'))]")),"")

Close this thread... and post in the VBA sub-forum, where it will show up as having no replies.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

I had thought that the TRANSLATE needed to be repeated endlessly.... if not, then you MIGHT manage with the formula... I think XPath allows for 4096 bytes.

I think it DOES need to be repeated. See row 14... It does not exclude Mpl

Is it necessary to include the case? Could you just not use LOWER($A2) instead of $A2?

You can shorten somewhat by only translating the characters in each criterion - for example:

Please Login or Register  to view this content.

rather than:

Please Login or Register  to view this content.

Unfortunately XPath 1 doesn't allow things like matches()

@rorya great point.

Xpath length at 1024 character

if you need to remove 30+ substrings then use nested FilterXML
where F2:F39 are lower case substrings

Please Login or Register  to view this content.