Counting words

Hi,

I'm trying to find a way to calculate the number of words in each of the names listed in Column A, so I can sort the names based on word count.

The formula I was given by a kind soul on another forum doesn't work. It gives errors and an incorrect count for certain names.

=LEN(A2)-LEN(SUBSTITUTE(A2;" ";""))

The semi-colon is used instead of a comma, which didn't work at all.

This is a sample of the list:

Haidan Zhong
Haiqin Lao
HaiYen Lim
Hamilton Franco
Hamish Jonathan Hay
HanBin Tan
Handi Ajimasta
Handian Lo
HangWei Low
Hanjie Lee
HanKit Lin
HanLei Chong
HanLing Tan
HanMing Huang
Hanna Katerina Tantoco
Hannah Claire Day
Hannah YinLai Leong
HanQuan Luo
HanTang Wai
Hanwei Tan
HanXiang Sng

Any ideas how to show the word count in column B, so I can sort them (names with two words first, then three words, and so on)?

Thanks.

Thanks. Even when I added +1, it gave errors. On the last row, for example, a red arrow was pointing to an empty cell. Am using Excel Mac 2011. Is that the problem?

About the formula with commas, it doesn't work at all.

Originally Posted by NoBob

the formula with commas, it doesn't work

That's just a regional settings thing.
Some regions use semicolon ( ; ) as a list separator instead of commas.
So anytime someone suggests a formula, and they use commas, you just need to change them to semicolons.

The formula works just fine for me.

Can you attach a sample workbook?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hi,

This is driving me mad! I just get an "you typed an error" when I use =SUM(IF(MID(A5;ROW($1:$50);1)=" “;1,0))+1

and the same with =IF(A2<>"",COUNT(AGGREGATE(14,6,FIND(CHAR(ROW(INDIRECT("65:90"))),A2,2),{1,2,3,4,5}))+1,"")

I want to treat 'HanXiang Sng' as two words.

Workbook1.xlsx

Okay,

I tried =IF(A2<>"",COUNT(AGGREGATE(14,6,FIND(CHAR(ROW(INDIRECT("65:90"))),A2,2),{1,2,3,4,5}))+1,"") in Windows Excel 2013, and it works, but it doesn't work in Mac Excel 2011.

I can't get =SUM(IF(MID(A5;ROW($1:$50);1)=" ";1,0))+1 to work in Windows Excel 2013 either. I still get an error.

About the formula =IF(A2<>"",COUNT(AGGREGATE(14,6,FIND(CHAR(ROW(INDIRECT("65:90"))),A2,2),{1,2,3,4,5}))+1,""), how do I get 'HanXiang Sng', 'HanLei Chong' and 'HanLing Tan' to show as two words?

Thanks.

Thanks, but =SUMPRODUCT((MID(A2,ROW(1:86),1)=" ")*1)+1 doesn't seem to work in either Mac Excel 2011 or Windows Excel 2013. Names like 'HangWei Low', 'HanLing Tan' and 'Hannah Claire Day' all show as 1, but 'Hamish Jonathan Hay' as 3, and 'Haiqin Lao' as 2.

That works! Thanks!

Originally Posted by AlKey

You were already given all kind of formulas that work.

Please see attached file.

Thanks. Yes, I thought I'd just posted a message about that (is there an icon for scratching head and thinking where did my reply disappear to?). =IF(A2="","",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1) works, too. Thank you.