Search Col.A for words that contain charactrs in Col.B & list all matches adjacently

Hey All, Is there anyone who can help me with the query below?

I have a column that list a series of Mandarin (Chinese) words (Column A), adajacent to that column I have a list of individual characters (Column B). I want to search Column A for all words that contain characters in Column B and return all matching words (comma seperated) in Column C adjacent to each character,

Example below (or see attachment):

WordsCharacter Result
后天 后 后天，后边， 以后
后边 明 明天，明年，明堂
明天
明年
以后
明堂

Any code/formulae that would help would be much appreciated.

Depends on how much you have to do.

If it is only a few words then this might give you a start.

Try this

Please Login or Register  to view this content.

It's a bit akward debugging chinese characters in VBa, your characters will show as "?" and in this case words will be "??" and the result e.g. "?? , ?? , ??", but it all seems to work.

Run the macro on sheet "Formula Solution - Marcol" to test

Hope this helps

[EDIT]
This should be fine for a few thousand lines, but if your data runs to tens of thousand rows, then a find/find next loop will be faster executing.

Happy to help