Remove word from string with one or more condition

Question

If match part of word in string, how to remove that entire word?

• Input: "This is a beautiful text in string."
• Rule: If in string exist "autif", then delete "beautiful"
• Result: "This is a text in string."

Answer 1

You can use:

Formula in B1:

=TEXTJOIN(" ",,FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[not(contains(translate(., 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz'),'autif'))]"))

It's an CSE-entered formula for Excel-2019.

Note: This could have an impact on punctuation.