Search Function with exact string without spaces

Question

I am trying to write a Excel function that filters an array based on the text of one column. I have everything working as intended except when I search for MOVE as opposed to REMOVE. I want the "Move" search to only return rows with "move" and not "remove"

I have seen other examples that include spaces but my cells do not have spaces so I cannot use this trick.

This is a simplified example of what I am trying to do. I would like this formula to only display ORANGE and not display APPLE.

Data:  
APPLE REMOVE  
ORANGE MOVE  

Formula:

=FILTER(A:B,ISNUMBER(SEARCH("MOVE",B:B)),"")

Results:  
APPLE REMOVE  
ORANGE MOVE

Desired Results:  
ORANGE MOVE

Answer 1

Try using the following formula by adding an empty string beside the conditions:

---

• Formula used in cell C1

=FILTER(A1:B2,1-ISERR(FIND(" MOVE "," "&B1:B2&" ")))

---

Also I would suggest you to use Structured References aka Tables instead of using the whole ranges in your formula, honestly it slows down the working capacity of Excel, since you will be iterating the functions to perform the task for blank rows as well. Which is why it is always asked to avoid.

---

=FILTER(Fruits,1-ISERR(FIND(" MOVE "," "&Fruits[Header 2]&" ")))

---

In place of 1-ISERR( you can also use ISNUMBER()

=FILTER(Fruits,ISNUMBER(FIND(" MOVE "," "&Fruits[Header 2]&" ")))

---

One more alternative method using TEXTBEFORE()

---

=FILTER(Fruits,TEXTBEFORE(Fruits[Header 2],"MOVE")="")

---

Precisely to show more clarity in my answer:

---