OpenOffice Calc does not provides an easy way to calculate number of words in a cell. However, there is a simple formula that you can use to easily count number of words in a cell in OpenOffice Calc.
Here is the formula:
Let’s say you want to count the number of words in cell B2 in your OpenOffice.org Calc excel. Then, you can put the following formula in any cell in the sheet:
=LEN(TRIM(B2))-LEN(SUBSTITUTE(B2;” “;””))+1
This will show you the word count in the cell in which this formula is placed. It ignores spaces, so it gives an accurate word count.
The way it works is as follows:
Step 1: Find total count of all the characters in the cell. This includes spaces as well. However, spaces in the beginning and end are ignore (this is why the word “TRIM” is used in the formula; it trims spaces in the beginning and at the end).
Step 2: Find total count of all the characters in the cell without including spaces. To achieve this, first all the spaces are removed (that is why “SUBSTITUTE” function is used in formula). Then total characters are counted in the cell.
Step 3: Subtract the count of Step 2 from Step 1, and add 1 to that.
Let’s try to understand this with an example:
Let’s say my cell has text “I Love Free Software”. So, the answer I would expect is 4 words.
Step 1 would count 20.
Step 2 would count 17
Step 3 would subtract 17 from 20 and add 1, so answer would be 4, and this is what I wanted.
So, this formula uses a few functions together to find number of words in a cell of OpenOffice Calc sheet.
In case you use Microsoft Excel, you can use following formula to count number of words in a cell in Excel:
=LEN(TRIM(B2))-LEN(SUBSTITUTE(B2,” “,””))+1
It looks same as the formula for OpenOffice Calc, but semi colons in that formula have been replaced with commas in this. Logic remains same as well.