Spreadsheet Formula to Count Words in Cell
Excel or OpenOffice Calc Formula to Count Characters in a Cell
Apr 19, 2009
One of the fundamental differences between Microsoft Excel and the OpenOffice Calc application is in the use of macros and formulas. While Calc is a long way from achieving compatibility in the use of the VBA (Visual Basic for Applications) language for macros, many problems can be solved by using formulas.
However, while it is impossible to create complex formulas, involving function calls, which will work in either application without modification, the two are close enough to make porting formulas between platforms possible. One such example is a formula for counting the number of words in a cell.
It might seem a simple enough proposition : after all, all that is required is to count the number of spaces. The usual approach taken by many programmers is to try and create some kind of CountCharacters macro function to count the number of ocurrences of a given character in supplied text.
While this will work, it is not easily portable between Calc and Excel, and there is a simpler, formula based approach that is much easier to port.
Formula to Count Words in a Cell for Excel or Calc
With a few minor changes, the following will work equally well in either Excel or Calc spreadsheets.
The key to understanding this formula is to take it apart, function by function.
The If Statement
Unlike traditional If statements, the If statement in both Excel and Calc is designed to evaluate a condition, and then return a value based on the veracity of that condition. In the case in hand, this means that the If statement is being used to evaluate the contents of the cell in question (in thos case A1).
If A1 is empty, based on a calculation of it's length, zero is returned. Otherwise, the next statement is evaluated.
The Substitute and Trim Functions
At the inside of this statement is the Substitute function, which replaces characters of a given character (" " in this case) with another ("", the empty string in this case). All that the statement does is remove the space characters to render the contents of the cell, minus the spaces.
The Trim function is used to remove extraneous space characters either at the start or end of a string. Of course, there is no need to Trim the string that is fed to the Substitute function, which will remove the spaces anyway, but it is necessary to Trim all the others, to avoid errors in calculation.
The Len Function
The final part of the formula is to calculate the difference between the two strings - the raw cell value without leading/trailing spaces, and the Substituted string with no spaces at all - to yield the number of separating spaces. This value is equal to the number of words : except that it is necssary to increase it by one to get the right number of words.
Differences Between Excel Formula and Calc Formula
The main noticable difference is that Calc requires that parameters in calls to functions are separated by semi-colons (';'). In Excel, the paramaters must be separated by commas (','). Aside from this, the formula ought to work with very little adjustment.
The main point to take away from the discussion of the formula itself is that it approaches the problem from a slightly different angle - taking care of the zero length problem, stripping extraneous spaces, then performing the substitution and length difference. This is different to the traditional approach of trying to create a macro to solve the problem.
