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
>Solution :
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:
