Forum Discussion
Solved
Excel Formula | Filter out a word from a table of words in another sheet Hi MS Community,
Hi MS Community, I have the following worksheet. In the Orders Tab, I need to be able to filter out the "Fruit" from the "Sentence". Sheet1 = Fruits Sheet2 = Orders In Fruits: ...
here are 2 options:
=LET(a,TEXTJOIN(",",1,IF(ISNUMBER(SEARCH($I$1:$I$3,A1)),$I$1:$I$3,"")), IF(a<>"",a,"invalid"))or
=IFERROR(TEXTJOIN(",",1,TEXTSPLIT(LOWER(A1),TEXTSPLIT(LOWER(A1),LOWER($I$1:$I$3),,1),,1)),"invalid")where A1 is the sentence cell and I1:I3 is the list of fruit
notice that the first uses SEARCH so case ignored but needed the extra step if nothing found. in the second I could enclose an IFERROR but needed to convert everything to LOWER() to make case indepenent
Thanks for the respond.
Just a question regarding the "$I$1:$I$3" portion.
If from the "Fruit" table, can I exclude some rows?
For below example, I want to exclude the "chicken"
So like "$I$1:$I$3 & $I$5:$I$6""
The reason I can't change the "Fruits" table is becuz it is used for other things.
A
1 apple
2 pear
3 banana
4 chicken
5 orange
6 kiwi
Thanks in advance
Just a question regarding the "$I$1:$I$3" portion.
If from the "Fruit" table, can I exclude some rows?
For below example, I want to exclude the "chicken"
So like "$I$1:$I$3 & $I$5:$I$6""
The reason I can't change the "Fruits" table is becuz it is used for other things.
A
1 apple
2 pear
3 banana
4 chicken
5 orange
6 kiwi
Thanks in advance
yes but not like that. either create a 'helper table' where you do that or use something like:
VSTACK($I$1:$I$3,$I$5:$I$6)
CHOOSEROWS($I$3:$I$6,1,2,3,5,6)
FILTER($I$3:$I$6, $I$3:$I$6<>"chicken")
in the 1st option above you can do any of the above options as another variable in the LET so something like:
=LET(list, CHOOSEROWS($I$3:$I$6,1,2,3,5,6),
a,TEXTJOIN(",",1,IF(ISNUMBER(SEARCH(list,A1)),list,"")),
IF(a<>"",a,"invalid")
)
VSTACK($I$1:$I$3,$I$5:$I$6)
CHOOSEROWS($I$3:$I$6,1,2,3,5,6)
FILTER($I$3:$I$6, $I$3:$I$6<>"chicken")
in the 1st option above you can do any of the above options as another variable in the LET so something like:
=LET(list, CHOOSEROWS($I$3:$I$6,1,2,3,5,6),
a,TEXTJOIN(",",1,IF(ISNUMBER(SEARCH(list,A1)),list,"")),
IF(a<>"",a,"invalid")
)
- Cool, thanks for your help!