Search for many words in a cell and return exactly the same words if they exist

[jorj1991]

Hello, I hope you are all well. I want to search about 50,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 50 thousand (50,000) words.

[#2]

@jorj1991 

You can do this with Power Query as explained in this video.

Multiple Key Words Search with Power Query - YouTube

[#3]

Thanks for your response. I can't add the words one by one in the column because there are about 50 thousand words, each word is separated by a comma and it is one after the other in Word. Can I easily enter each word in a column with an operation or function?

[#4]

@jorj1991 

=FILTERXML("<t><s>"&SUBSTITUTE(B2,",","</s><s>")&"</s></t>","//s")

You can try this formula. In the example the formula is in cell D6. Then i dragged the formula across range D6:D45 and confirmed with ctrl+shift+enter.

The example is for a small number of words. In my understanding it should work for a huge number of words as well.

[#5]

@jorj1991 

Re:  Extract Certain Words

This is a much different approach.

It used the free workbook "Professional_Compare" which has multiple utilities

including "List All Words".

It lists all of the words from multiple cells, with the count, in a separate sheet.

The example used 50,000 cells with three words in each cell...

List of words (truncated)...

Separate List of Extracted Words - listed in order by count (truncated)...

(just a few seconds required to generate the list)

Download 'Professional_Compare' from OneDrive...

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

'---
Nothing Left to Lose