Compare column of word strings to a word document

**cleanenergy** · 12-30-2010, 09:47 AM

Is it possible to create a formula in Excel that will compare (and count) the instances of a text string in column A to text in a word doc (or word doc)? For example; column A might contain 30 cells each with different strings. I would like each of those strings searched for within a defined text document for their appearance. I would like the result of the count located to be recorded in column B.

thanks in advance
Frank

**6StringJazzer** · 12-30-2010, 12:59 PM

You can't do this with formulas. You would have to write a macro in VBA that would open the Word document and scan each word for a match, or do repeated finds, or something like that. How much effort is this worth?

**cleanenergy** · 12-30-2010, 04:14 PM

I have a number of articles to manage daily and I must find an easier way to manage the relationship between these articles and the many long lists of words (and word phrases).

Finding, counting, and reviewing each word (or phrase) has become tedious work going between applications to select a string, search for it, mark it, then review the use of it.

If there is a better way I'm all for it, although I'd have to lobby for the change of processes here, but if it's more efficient that should be easy.

Any thoughts?

**optkontek** · 12-30-2010, 05:55 PM

This is doable in Excel without VBA. First, export your word file as ascii text (.txt). Then import this file into a new sheet in Exce (here: Sheet2)l, using the Data - Import text file tool. Make sure you make the file delimited, but remove all delimiters in the text file. Then each line in your text file will now be placed in a cell in your spreadsheet.

Now, put your list of expressions in column A in another sheet (Sheet1). Then, in column B, you can use countif to do this, together with some wildcards for matching partial strings. The formula you would put in cell B1 would be:

=COUNTIF(Sheet2!A:A,CONCATENATE("*",A1,"*")).

Just tried it on a 40-page document. Quite nice actually - thanks for the question, this will come to good use! :-)

BTW, I tried this on an article on cnn.com - works very well and quite fun. "Tea Party" seems to be the repeating term in articles on the GOP, the account of this ground-breaking research can be found at http://optkontek.blogspot.com/2010/1...r-phrases.html

**martindwilson** · 12-30-2010, 06:59 PM

although concatenate is useful its just easier to type "*"&a1&"*"
=COUNTIF(Sheet2!A:A,"*"&A1&"*")

**6StringJazzer** · 12-30-2010, 08:17 PM

Originally Posted by optkontek

...Then each line in your text file will now be placed in a cell in your spreadsheet.

This is a great idea, never would have thought of it. But won't it miss counts if the same word appears more than once in a line? Maybe importing it so it's one word per cell...?

**optkontek** · 12-31-2010, 06:11 AM

That's a good point. One should really use whitespace as a delimiter then :-) Thanks for correcrting my suggestion!

**cleanenergy** · 12-31-2010, 04:02 PM

Gee thanks!

I always find it amazing how some people make some things look so easy. I have moments of course, it's just never while coding.
Happy New Year!