1

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."
Losai
  • 329
  • 2
  • 9
  • Why not just substitute “beautiful” out regardless given that’s the word you want to remove anyway? – Skin Mar 13 '22 at 10:38
  • @Skin Because its part of big data ranges with multiple conditions – Losai Mar 13 '22 at 10:41
  • You have a few questions where substitute() occurs in the answers - perhaps you should check it out more. – Solar Mike Mar 13 '22 at 11:45
  • 2
    So would you want to delete all words containing that substring? Beautification, beautifull, etc etc. Also, best to update your question to include your attempted formulae to make it on topic like your previous questions. – JvdV Mar 13 '22 at 11:56
  • @JvdV Yes, all the words by matching1, then all words by matching2, then all words by matching3,.. – Losai Mar 13 '22 at 11:59
  • 2
    Don't change questions to the point that it would require complete new answers. I'm sorry but that's against the rules of SO. I rolled back the version for you. I did see that you had another question posted just like that. It's closed and you deleted it, but it also said why it was closed. It is lacking your own attempt. I honestly think you have a nice question at hand but without your own attempt it will be off-topic I'm afraid. If you can create a question according to SO standards I'd be happy to answer it – JvdV Mar 14 '22 at 06:56
  • 1
    That was not a moderator, but another user that saw (rightfully) a lot of similarity between these questions. This question is focused on just a single substring, e.g.: `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 [ask] a question with a [mcve]. – JvdV Mar 14 '22 at 07:35

2 Answers2

2

You can use:

enter image description here

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.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    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) – Losai Mar 13 '22 at 12:53
  • 1
    @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 Mar 13 '22 at 14:01
  • 1
    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. – JvdV Mar 13 '22 at 14:17
  • Just for three alternative I can use only consecutive SUBSTITUTEs – Losai Mar 13 '22 at 14:20
  • 1
    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. – JvdV Mar 13 '22 at 14:21
  • Because I have much more then three matches, its about 30 word trunks to remove hundred words – Losai Mar 13 '22 at 14:52
1

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", "")
Skin
  • 9,085
  • 2
  • 13
  • 29
  • 1
    Thank you. The first formula give me exact answer to my problem, but you right its an extra calculation – Losai Mar 13 '22 at 10:52