[SOLVED] Excel Equivalent of Python "Not In" to return a string stripped of unwanted characters

Excel Equivalent of Python "Not In" to return a string stripped of unwanted characters

On Sheet 1, Column B contains some words with bad characters. Sheet2 has a large range of all of the characters I don't want. In python, I would use a list comprehension and "not in" to return the list of words removing any bad characters. What is the equivalent Excel command?

I have tried doing this with regular expressions and a simple nested loop in a VBA UDF, but the bad characters are non-Unicode (Japanese), and it is choking on converting them. I am hoping there is some combination of Substitute, etc. That can return the word stripped of any bad characters. Alternately, if anyone knows how to approach converting Japanese characters in VBA, that would also work.

Thanks!

Solution

Solved this by using a web page to batch convert the Japanese characters to "Unicode non-character references", which are codes that each represent a character from a non-unicode set.