True Word Count

**VegasL** · 09-26-2011, 12:05 PM

Hello,

I am trying to find the "true" word count of a cell. Please see attached file. I need it to exclude any numbers, characters, etc, just count real words.

Formula IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1) returned 9, but it should return 6, when counts only # of real words.

Is this possible?

Thanks

**NBVC** · 09-26-2011, 12:21 PM

It is not easy to do because SUBSTITUTE doesn't allow to substitute a bunch of possibilities at once.

You will need VBA or if you can download a free addin from here: Morefunc then apply formula

Please Login or Register  to view this content.

**Andrew-R** · 09-26-2011, 12:35 PM

Possible? Yes, but not really easy - certainly not with a formula.

Here's your workbook back with an Excel module in containing the following code, which can be used as a worksheet function (and which snb will be along to rewrite in one line in just a minute

)

Please Login or Register  to view this content.

**VegasL** · 09-26-2011, 12:46 PM

Thanks guys. Appreciate the help. Using Excel 2010 so MoreFunc wouldn't work on there. Andrew R where do you plug this code in to make it work?

Again thanks to both of you!

- Andrew, ok I went into excel view code, on the file you sent, but its blank. But obviously it works, so how/where do you make it run in excel 2010? I went into excel 2010, with same formula, but it didnt do anything, any help on this would be appreciated.

**Andrew-R** · 09-26-2011, 01:30 PM

Instructions for use:

1. Open the workbook you want to use the function in and press Alt-F11 to open the VB editor window.

2. From the insert menu choose "Module"

3. A blank module window will open, copy my code from above and paste it into the module. You can now close the VB editor and use the WordCount function on that worksheet.

Please note that the function is very rough and ready and may have a few annoying features. It works by splitting the text you give it into a number of elements of a string array and then counts the number of elements that contain only the letters a-z and A-Z. Take an example sentence...

The Excel macro language (VBA) is a useful, easy to learn, object-oriented language.

If we just split on spaces that sentence will generate the words:
The
Excel
macro
language
(VBA)
is
useful,
easy
to
learn,
object-oriented
language.

However, a simple count of the words that contain only letters will exclude "(VBA)", "useful,", "learn,", "objection-oriented" and "language." because they all contain non-alphabetic characters.

The code gets round this by replacing punctuation characters with spaces prior to splitting the words, hence all of the above words will be counted, but object-oriented will be counted as two words (which, to be fair, it is), but also things like "e.g." and "i.e." will be counted as two words.

If you can't live with those limitations then I suppose the code could be expanded.