Counting words in a single cell in Numbers'09

Question

Counting words in a single cell in Numbers'09

Hi there,

I'm relatively new to Mac world, but I do have years of computer experience from a PC and have also had to do with Macs at the age of first eMacs 😉. I have finally decided to switch to the brighter side of life (hopefully ;)).

But here is my question: I need to count words in a cell in Numbers'09.
Is there a specific function combination for achieving this? My idea was: strip excessive spaces, count the occurencies of all space character in a cell, add 1 and voila! Problem is I can not achieve it using formulas in Numbers'09. I have found some help for Excell but the formulas are a little different. And well, I would like to leave the past behind 😉 and stick to a Apple programs - if I can. I don't like the idea to install Excell on a Windows Bootcamp partion only for this purpose.

Any help would be greatly appreciated. Thanks.

Aleksander

iMac 9.1 24", Mac OS X (10.6.2)

Posted on Mar 1, 2010 12:48 AM

Reply

Answer 1

Badunit

Level 7

30,456 points

Mar 1, 2010 4:51 AM in response to Aleksander Bednarczyk

Sounds like you were on the right track.

B2 =original string
B3 =TRIM(B2) ....remove extra spaces
B4 =SUBSTITUTE(B2," ","") ....remove all spaces
B5 =LEN(B3)-LEN(B4)+1 ....difference in length equals the number of spaces. Add one to count words.

You could do this all in one formula; I broke it up for clarity.

Reply

Answer 2

Mar 1, 2010 5:27 AM in response to Badunit

Hallo Badunit,

Thank You for Your help. It is comforting that I'm on the right track. It may be even working 😉 but...

My (additional) problem is that I'm using Polish version of iWork'09 therefore the functions have been completly translated and the English formulas are simply not recognized. Part of the formula names I guessed looking in context Help but the SUBSTITUTE function seems to be translated weirdly or have different syntax due to different delimiter (?).
Does one know how to turn English version of formula names back on? or where can I find the translation list for the functions? I know it is probably out of the range of this topic but...

Aleksander

Reply

Answer 3

Mar 1, 2010 5:34 AM in response to Aleksander Bednarczyk

Aleksander Bednarczyk wrote:
Does one know how to turn English version of formula names back on? or where can I find the translation list for the functions? I know it is probably out of the range of this topic but...

Aleksander,

The brute force method would be to set up another user account and set all language preferences in that account to US English. Doing your testing in that account would eliminate problems with language and when you are done, you don't have to change anything back for your normal operations, just go back to your original account and continue.

Jerry

Reply

Answer 4

Badunit

Level 7

30,456 points

Mar 1, 2010 5:40 AM in response to Aleksander Bednarczyk

Yvan once had a list of all the different localizations. He may still have it.

Your delimiter may be the semicolon, not the comma.

Reply

Answer 5

Mar 1, 2010 6:12 AM in response to Aleksander Bednarczyk

OK! I have managed to work it out by try and error:

Polish version would be something like this:
B2 is original cell
TRIM(B2) is USUŃ.ZBĘDNE.ODSTĘPY(B2)
SUBSTITUTE(B3; " "; "") is PODSTAW(C2; " "; "")
LEN(B3)-LEN(B4)+1 is DŁ(B3)-DŁ(B4)+1

so one question only remain: how to combine it in one cell?

=DŁ(USUŃ.ZBĘDNE.ODSTĘPY(B2))-DŁ(PODSTAW((B2;" "; ""))+1)

doesn't do the job. I probably forgot something...

Reply

Answer 6

Mar 1, 2010 6:50 AM in response to Aleksander Bednarczyk

Aleksander,

Try: =DŁ(USUŃ.ZBĘDNE.ODSTĘPY(B2))-DŁ(PODSTAW(B2;" "; ""))+1

Jerry

Reply

Answer 7

Mar 2, 2010 12:18 AM in response to Jerrold Green1

Jerry,
that is it! I don't know how could I have missed that additional () though 🙂

One more thing - due to +1 in the formula all empty cells will have the value 1.
I have solved this by additional modyfing:

=JEŻELI(DŁ(Zadanie konkursowe '1')=0;0;DŁ(USUŃ.ZBĘDNE.ODSTĘPY(Zadanie konkursowe '1'))-DŁ(PODSTAW(Zadanie konkursowe '1';" ";""))+1)

JEŻELI means IF

Thank You all of You who helped 🙂

PS. That function translation matrix would be handful for future reference.

Reply

Answer 8

Mar 2, 2010 4:36 AM in response to Aleksander Bednarczyk

Aleksander,

Thank you for the feedback. Really, badunit deserves the credit. I just changed the phrasing a bit for you.

Jerry

Reply

Answer 9

Mar 2, 2010 4:46 AM in response to Jerrold Green1

Jerry - You are right 🙂

@Badunit: sorry I can give just one Star in this Post - but You deserve it fully.

THX again,

Aleksander

Reply

Answer 10

Badunit

Level 7

30,456 points

Mar 2, 2010 5:42 AM in response to Aleksander Bednarczyk

No problem. I like stars but I'm really just here to help.

Reply

Answer 11

KOENIG Yvan

Level 8

41,819 points

Mar 2, 2010 9:31 AM in response to Badunit

Badunit wrote:
Yvan once had a list of all the different localizations. He may still have it.

I'm late but, I was very busy 😟

The table with every localized functions names is (and will remain) available on my iDisk :
<http://public.me.com/koenigyvan>
Download :
For_iWork:iWork '09:functionsNames.numbers.zip

An easy soluce for foreign users (like me) is to duplicate Numbers.app and remove its languages resources minus English.
Running it you will have it running in English (minus the decimal and the parameters separators, minus also date time formats and default currency).
It would be easy to enter the formulas given in this forum.
Once saved, we may open the doc in the 'standard' Numbers and the formulas will be automatically localized.

Yvan KOENIG (VALLAURIS, France) mardi 2 mars 2010 18:30:45

Reply