Searching for multiple words in a cell

**CU2011** · 05-03-2011, 10:06 AM

Here it is...

I've never used macros before so please excuse my complete igorance of the subject! I'm wondering if it's possible to have the program identify exact matches of the keyword (such as capitalization)? Also, I noticed when I repeat this process with an extremely large file, say 3000 rows of text, the macro debugger pops out - is there a size limitation to the operation of this macro?

Thank you so so much!

**NBVC** · 05-03-2011, 10:23 AM

Try:

=SUBSTITUTE(SUBSTITUTE(TRIM(ACONCAT(IF(ISNUMBER(FIND($C$2:$C$17,B3)),SUBSTITUTE($C$2:$C$17," ","^^"),"")," "))," ",", "),"^^"," ")

confirmed with CTRL+SHIFT+ENTER not just ENTER.

This is case sensitive so if the string in column B has "congress" it will not pick up as a match to "Congress" in column C.

I fixed it up so that you can still have comma delimiters, but it won't affect multi word entries like "Unites States".

In some of your comments, you don't include "Congress" as being an expected match, like in D2.... but "Congress" does exist in B2.

You also say that D15 should be blank, but I find that some of the words in column C do, in fact exist in B15 (and same with B16/D16 in case you are off by 1 row in your references in the comments).

Also, I did copy the formula down to past 3000 rows and got no errors.... you just have to wait for the recalculating to finish as this is an array formula and can be slow when copied down thousands of rows.