Forum Discussion

jorj1991's avatar
jorj1991
Tin Contributor
Oct 30, 2022

Return the search value

I hope you are fine. I have asked this question twice before, but the answers were not suitable for me. I don't want to do this with Power Query. I want to search all 10064 words in column j in eac...
excel
dscheikey's avatar
dscheikey
Bronze Contributor
Oct 30, 2022

jorj1991 

Hello please try this formula with FIND() and FILTER().

 

=IFERROR(TEXTJOIN(",",TRUE,FILTER($J$2:$J$10065,IFERROR(FIND($J$2:$J$10065,A2)>0,"")=TRUE)),"")

 

Good luck!

  • jorj1991's avatar
    jorj1991
    Tin Contributor
    Nov 11, 2022
    Hello, thank you for your reply and the time you took to help me. I apologize for the delay in replying. I tested your formula. I don't want to do it with the find function because it is case sensitive. I did your formula with the search function. Compared to the formula below, it takes about 6 times the time. And the results of this formula with your formula in the number of characters in the test sample I have exactly the same size.
    =TEXTJOIN(", ", TRUE, IF(COUNTIF(A2, "*"&$I$2:$I$37&"*"), $I$2:$I$37, ""))

    "I" is the column to be searched in cell "A2".


    Thank you again dear friend