Search code examples
exceltextsplitformula

Find word in text and remove the text that contained the word

I've set of data that need to find certain word in text in excel, and if the text has this word, need to remove the whole text.

previously I was using text to column to do this, but the problem sometimes the position of the word is not same, such as have extra number. Kindly refer below for example.

example :

  1. 1013-2329308 1013EFECTIVO0002046 0221901
  2. 1013-3747097 1013EFECTIVO0002060 0123123
  3. 1013-37274979 1013EFECTIVO00020615 0136659

What I'm trying to do is to find word EFECTIVO in text and the remove the text including before the text. In above example.

the final result will be like this.

  1. 0221901
  2. 0123123
  3. 0136659

enter image description here

enter image description here

Solution

  • You may try-

    =IF(ISNUMBER(SEARCH("EFECTIVO",A1)),TEXTAFTER(A1," ",-1),"")

    enter image description here