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
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
=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
