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

=(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-(LEN(TRIM(REGEX.SUBSTITUTE(A2,"[a-z,A-Z]","")))-LEN(SUBSTITUTE(TRIM(REGEX.SUBSTITUTE(A2,"[a-z,A-Z]",""))," ",""))))

**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

)

Function WordCount(ByVal sPhrase As String) As Long

Const sBREAKINGCHARS = "-!.,&+*()[]{}@#?;:'£$%_"""

Dim asWords() As String
Dim lTmpCount As Long
Dim lLoopVar As Long
Dim lWordLoop As Long
Dim lCharLoop As Long
Dim lThisChar As Long
Dim bIsWord As Boolean

For lLoopVar = 1 To Len(sBREAKINGCHARS)
  sPhrase = Replace(sPhrase, Mid(sBREAKINGCHARS, lLoopVar, 1), " ")
Next lLoopVar

While InStr(sPhrase, "  ") > 0
  sPhrase = Replace(sPhrase, "  ", " ")
Wend

asWords = Split(sPhrase, " ")

lTmpCount = 0

For lWordLoop = LBound(asWords) To UBound(asWords)
  bIsWord = True
  lCharLoop = 1
  While lCharLoop <= Len(asWords(lWordLoop)) And bIsWord
    lThisChar = Asc(Mid(asWords(lWordLoop), lCharLoop, 1))
    If Not ((lThisChar >= Asc("A") And lThisChar <= Asc("Z")) Or (lThisChar >= Asc("a") And lThisChar <= Asc("z"))) Then
      bIsWord = False
    End If
    lCharLoop = lCharLoop + 1
  Wend
  If bIsWord Then
    lTmpCount = lTmpCount + 1
  End If
Next lWordLoop

WordCount = lTmpCount

End Function

**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.