0

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!

Michael James
  • 492
  • 1
  • 6
  • 19
  • Some sample data, as well as the VBA you've already tried, would be helpful in answering this question, thanks! – BigBen Oct 04 '19 at 01:14
  • agree with BigBen that you need to show some sample data (with all possible variations and "bad" characters) and the expected results. Having that said, I think it can be done using excel either by formula or vba if the scope is defined clearly. – Terry W Oct 04 '19 at 01:30

1 Answers1

0

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.

Michael James
  • 492
  • 1
  • 6
  • 19