Forum Discussion
Excel Formula | Filter out a word from a table of words in another sheet Hi MS Community,
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
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")Hello m_tarler , I'm back again.
So I tested around your formula and came across another issue. If you can see the table sample below, it should explain. Basically the code I should get from the Contract should be NJM, but becuz of the "Japan", it got JA as well.
Is it possible to ensure that the casing are matching as well? (Since all the codes are in uppercase).
Im using the "=LET" by the way
Tyvm
ListofCode NJM MPS 32 JA E6 E6M Contract getCode Naphtha C&F Japan Mini [NJM] - ICE NJM Block Nov-24 NJM, JA
- 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")
)- Cool, thanks for your help!
