Help with creating a text analyser spreadsheet

[islanddave]

Hello all!

Is there a way to make a simple text analyser that will return the number of times each word is used in a sentence or paragraph within a cell?

I use excel to write short paragraphs to upload onto social network sites and I would like a formula or program that tells me how many times each word has been used over a number of cells, to avoid repetition. Is there a way to do this?

Thank you

Dave

[Pete_UK]

Suppose you have your responses in column A starting in A1, and you want to check for the word "the" - you can use this formula:

=(LEN(A1)-LEN(SUBSTITUTE(A1,"the","")))/LEN("the")

which will give you the number of times the word occurs in A1. You can replace "the" with a cell reference that contains that word. The formula will count the word "theatre" as containing that word, so to avoid this happening you can use " the " with spaces around.

Hope this helps.

Pete

[islanddave]

Thanks Pete, it does help to a certain extent, but is there a way to find the number a times each word occurs in the paragraphs, without having to input the words required into the formula? (Very time consuming!)

Dave

[Pete_UK]

Okay, I had assumed that your first response was in A1, but if you insert two new rows at the top of the sheet you can use B2, C2, D2 etc. for the words you want to count, and row 1 for the total count (or average if you want). So, with your first response in A3, put this formula in B3:

=(LEN($A3)-LEN(SUBSTITUTE($A3,B$2,"")))/LEN(B$2)

This can be copied across and down to give you an individual count of the words in row 2 for each response. You can put this formula in B1:

=SUM(B$3:B$1000)

and copy across the row to give the total for each word (or change SUM to AVERAGE if that is a better measure for you).

Hope this helps.

Pete