'Remove word from string with one or more condition
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."
Solution 1:[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.
Solution 2:[2]
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", "")
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | JvdV |
| Solution 2 | Skin |