Search for many words in a cell and return exactly the same words.By writing formulas.Not Power Quer

[jorj1991]

Hello, I hope you are all well.

I don't want to do this with Power Query. I want to write with a formula that should probably have a search function in it. I don't want it to be case sensitive. I want to search all words.

 I want to search about 10,000 words or phrases in one column. If there are any number of these words, I want the exact same words to be returned to me in the opposite cell. There should be a comma between each word. I want one column. I have several tens of thousands of cells. I want to do this for each cell and write it in the opposite cell and put a comma between the words if there is more than one word so that they don't get confused with each other. Thank you for helping me with the formula. Consider some hypothetical search words, for example "house" and "book" and "clothes". But the number of my words is about 10 thousand (10,000) words.

[#2]

@jorj1991 

You have already asked this question.

Search for many words in a cell and return exactly the same words if they exist - Microsoft Communit...

[#3]

This message is from me a few days ago. I went with the Power Quarry solution, but it took about a day to complete one of my files. Due to the low RAM of my laptop, I need to write the formula in Excel itself and get the output. With my Power Quarry, it takes about two months to do this. but by writing the formula, I may search half a million in less than a week. So please, if anyone can help me with the formula, answer my message. Thankful

[#4]

@jorj1991 

=IFERROR(INDEX($AU$2:$AU$588,SMALL(IF(ISNUMBER(SEARCH($AU$2:$AU$588,$A2)),ROW($AU$2:$AU$588)-1),COLUMN(A:A))),"")

You can try this formula as shown in the example in the attached file. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

[#5]

@Quadruple_Pawn Can cells B9, C9, D9, etc. be found by putting a comma between each word in a house? A formula that puts a comma between the words and puts them all in one cell.

Thank you for your answer

[#6]

@jorj1991 

If you work with Excel 2019 or later you can try TEXTJOIN function.

[#7]

I have Office 365. Can you write the formula for me with this function?

[#8]

@jorj1991 

I have Excel 2013 Home and Student.

[#9]

@jorj1991 

You could use:

=BYROW(A2:A9,LAMBDA(row,TEXTJOIN(",",1,XLOOKUP(TEXTSPLIT(row," "),words,words,""))))

I'm a bit surprised PowerQuery is taking long to run on your computer. I don't know if the above will be any quicker but here it is.

[#10]

Ok, thank you very much. Multiply these numbers and you will understand the size of the project. Ten thousand words should be searched in 20 thousand cells, each level has about ten thousand words. This will be one of my files. I have about forty files. I think your formula is perfect, I will test it. Thank you for your answer

[#11]

Ok, thank you very much.