A Perhaps the simplest solution is to write a custom VBA worksheet function. But since you prefer a nonmacro solution, I developed a rather complex array formula. The number of words in a cell is equal to the number of space characters (character code 32), plus 1. Use the following array formula to return the number of words in a cell (in this case, Cell F9):
=sum(if(mid(trim(F9),row($A$1:offset ($A$1,len(trim(F9))-1,0)),1)=char(32), 1,0))+1
To enter an array formula, press <Ctrl>-<Shift>-<Enter> rather than just <Enter>. Excel identifies an array formula by placing brackets around the formula. Notice that this formula uses the ROW function, with a reference to Cell $A$1. This is a trick that generates an array of consecutive integers beginning with 1 and ending with the number of characters in the cell. This integer array is then used as the second argument for the MID function. The formula also uses the TRIM function, which eliminates any excess spaces that would result in an incorrect word count.
- John Walkenbach