Forum Discussion
Search for many words in a cell and return exactly the same words.By writing formulas.Not Power Quer
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.
10 Replies
You have already asked this question.
- 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
=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.
