A better way to count words


In February, I provided an array formula for counting the words in a cell (Count words in a cell, p166). Craig Woodard suggests a clever solution that uses the Substitute function instead. It works in Excel and Quattro Pro.

 

Caption: Count the number of words in a worksheet cell by using the Substitute function instead of an array formula

 

In Excel, to count the number of words in cell A1, use this formula:

=len(substitute(trim(A1),char(32),char(32)&char(32))) -len(trim(A1))+1

In Quattro Pro, use this formula:

@length(@substitute(@trim(A1),@char(32), @char(32)&@char(32)))-@length(@trim(A1))+1

The formula substitutes two spaces for every space, calculates the string length, then subtracts the length of the original string and adds one.

û John Walkenbach


Category:spreadsheet
Issue: June 1998

These Web pages are produced by Australian PC World © 1997 IDG Communications