Remove word from string with one or more condition-CodePudding

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."

CodePudding user response：

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.

CodePudding user response：

If you want to check for a string, use FIND and then SUBSTITUTE.

=IF(ISERROR(FIND("autif",A1)),A1,SUBSTITUTE(A1,"beautiful",""))

There's no need to search for it though, I can't see why you wouldn't just substitute it out regardless. Checking for it is a waste of time because if it exists, you will change it out, if it doesn't exist, you won't, so just remove it regardless ...

=SUBSTITUTE(A1,"beautiful","")

If you have multiple conditions, you can throw them all into one formula ...

=SUBSTITUTE(SUBSTITUTE(A1,"beautiful",""), "string", "")