I am doing this on another forum, too. I have some general knowledge of the worksheet functions in Excel, but I have begun work on one too big for me to manage well without some extra help. So here goes...
I'm working on giving Chinese vocabulary lessons in supplement to grammar tutorials.
However, in order to avoid errors in other language books books, I only want to introduce so many characters at a time into the vocabulary, but at the same time, only the words that contain the more frequent characters first.
I found that giving an example helps best.
Let's say that I have a vocabulary list in Column X:
幸好
能幹
好幾
幾個
幾乎沒有
幾何學
幾天來
幾個小時
好幾年
幾年來
有序
Now, I want to filter out all of the results unless they contain ONLY THESE CHARACTERS: 好, 幾, 年, 來. The characters, in order of frequency, are put in column Y2:Y2551.
If the filter is done correctly, then it should make something in Column Z:
好幾
好幾年
幾年來
Now, if I tried doing this long-hand in Column Z, it would read something like this:
=IF(NOT(OR(ISNUMBER(SEARCH(跑,X1)),ISNUMBER(SEARCH(工,X1)),ISNUMBER(SEARCH(忙,X1)),ISNUMBER(SEARCH(相,X1)),ISNUMBER(SEARCH(光 ,X1))...),X1,"")
The problem is that with only six characters, the equation would be ridiculously big, with 2496 of those ISNUMBER(SEARCH(Character in Cell Y,Cell X)). I don't think I could even fit 2500 words into the equation without causing an error.
Is there a method that allows me to enter multiple rows into the SEARCH() function? Is there any other potential shortcut that could get a product as I need it?
Bookmarks