counting words within cells

[melanie-h]

hi,

in excel i have a huge list of country names

eg.

A1: Austria, Italy, Sweden, France, Spain UK, Japan, USA
A2: France, Spain, Austria, Italy, UK, Germany, Sweden, Norway, Netherlands
...
A450

Instead of the country names, I need to know how many countries are in each cell

eg.

A1: 8
A2: 9

Is there a formula for this?

Thanks for the help

Mel

[oldchippy]

hi,

in excel i have a huge list of country names

eg.

A1: Austria, Italy, Sweden, France, Spain UK, Japan, USA
A2: France, Spain, Austria, Italy, UK, Germany, Sweden, Norway, Netherlands
...
A450

Instead of the country names, I need to know how many countries are in each cell

eg.

A1: 8
A2: 9

Is there a formula for this?

Thanks for the help

Mel

Hi melanie-h,

Try this in A2, then copy down

=FIND(", ",A1,1)+1

this assumes that each one is separated by a "," and a space

oldchippy

[starguy]

Hi melanie-h,

Try this in A2, then copy down

=FIND(", ",A1,1)+1

this assumes that each one is separated by a "," and a space

oldchippy

oldchippy, FIND function will not count number of occurences of any character, it simply returns position of character.

[starguy]

hi,

in excel i have a huge list of country names

eg.

A1: Austria, Italy, Sweden, France, Spain UK, Japan, USA
A2: France, Spain, Austria, Italy, UK, Germany, Sweden, Norway, Netherlands
...
A450

Instead of the country names, I need to know how many countries are in each cell

eg.

A1: 8
A2: 9

Is there a formula for this?

Thanks for the help

Mel

Hi melanie

try this
if your data is in cell A1 then
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))-1
this assumes that you have country name each seperated by a comma.

hope this would serve your purpose.
Regards

[SteveG]

Melanie,

You could download Laurent Longre's free Morefunc addin which includes the formula WORDCOUNT.

=WORDCOUNT(A1,","&" ")

Where A1 is your text and the ","&" " are your text separators. There are a lot of other very useful functions with the addin.


HTH

Steve
http://www.rhdatasolutions.com/morefunc/

[SteveG]

Melanie,

If you don't want or can't download the Morefunc addin you could use this formula instead:

=IF(ISBLANK(A1),0,LEN(TRIM(SUBSTITUTE(A1,CHAR(10)," ")))-LEN(SUBSTITUTE(SUBSTITUTE(TRIM(A1),CHAR(10),"")," ",""))+1)

This assumes that all words are separated by a space whether or not there is a comma.


HTH

Steve

[oldchippy]

Hi melanie-h,

Try this in A2, then copy down

=FIND(", ",A1,1)+1

this assumes that each one is separated by a "," and a space

oldchippy

Your right, I'll go back to sleep now