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."
2 Answers
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.
6 Comments
Losai
Very intresting way, using FILTER, instead of my way. But how to use it for multiple parts matching? First im thinking about: IFERROR(REPLACE(A1; FIND({"autif";"road";part"};A1);LEN(//length of that word//);"");A1)
JvdV
@losai, I see that as new requirements/new questions. But to give a clue, depending on how many different matches you need, this is rather easy with an extension on the xpath syntax.
JvdV
In xpath you can use 'or' inside the same square brackets. Unfortunately xpath 1.0 does not allow for arrays, but for just these three alternatives it's not too bad to repeat the logic three times.
Losai
Just for three alternative I can use only consecutive SUBSTITUTEs
JvdV
I don't understand why you mentioned three consecutive substitute functions. All can be done in a single but rather long xpath expressions using or logic.
|
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", "")
1 Comment
Losai
Thank you. The first formula give me exact answer to my problem, but you right its an extra calculation
autif. Where your first few questions did include your own attempt at solving your issue, your last few questions seem to lack that (notice also my 1st comment above). SO is based around questions where you'd include your own attempt to show what you tried. I'd suggest you open a new question with all the details, and to make sure it's on-topic, include your own attempt. For more reference see How to Ask a question with a minimal reproducible example.