How to count words on excel

How to count words on excel

Excel or OpenOffice Calc Formula to Count Characters in a Cell

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 occurrences of a given character in the 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.

=IF(LEN(TRIM(A1))=0;0;LEN(TRIM(A1))-LEN(SUBSTITUTE(A1;” “;””))+1)

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 this case A1).

If A1 is empty, based on a calculation of its 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 separate spaces.

This value is equal to the number of words: except that it is necessary to increase it by one to get the right number of words.

Differences Between Excel Formula and Calc Formula

The main noticeable difference is that Calc requires that parameters in calls to functions are separated by semi-colons (‘;’). In Excel, the parameters 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 from the traditional approach of trying to create a macro to solve the problem.

find thousands of excel spreadsheets here