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:
| A | |
| 1 | apple |
| 2 | pear |
| 3 | banana |
In Orders:
| Sentence | Fruit | |
| 1 | James buy apple | apple |
| 2 | Peter sell pear | pear |
| 3 | Banana from japan | banana |
| 4 | apple keeps the doctor away | apple |
| 5 | Do you sell durian? | Invalid |
Preferably, casing can be ignored. I was trying out Index & Match but it didn't work (pls ignore the rows and column number as I was trying to simply using fruits).
| =INDEX(fruit!A1:A3,MATCH("*"&fruit!A1&"*",C1:C4,0)) |
Kind Regards
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
9 Replies
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
Sorry I need ask another question after testing the code. I notice it shows all fruits in the sentence.
Is it possible to just have only 1 fruit, but have the flexibility to adjust which if necessary?
Sentence Fruit 1 James buy apple and pear apple 2 Banana and durian for lunch Durian Kind Regards
- Yes I specifically added the TEXTJOIN to show all the results. You could replace with INDEX( ... ,1) to only take the first but not sure what you mean by "flexibility to adjust which". I suppose you could just have them listed in priority of showing.
=LET(a,INDEX(IF(ISNUMBER(SEARCH($I$1:$I$3,A1)),$I$1:$I$3,""),1), IF(a<>"",a,"invalid"))
or
=IFERROR(INDEX(TEXTSPLIT(LOWER(A1),TEXTSPLIT(LOWER(A1),LOWER($I$1:$I$3),,1),,1),1),"invalid")
- 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- 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")
)
