Excel formula to count the number of words that ignores single lettered-phrases

[Karthik-Excelsior]

If I've a word - 'cheap t shirts' - in a cell, I want excel to count the number of words in the cell but ignore the single-lettered word (in this case 't'). So, ideally, COUNT(cheap t shirts) should give me 2 instead of 3. Is there an excel formula to that effect?

 

[Peter_SSs]

You could try this user-defined function. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy down.

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> CountWords(s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Static</SPAN> RegEx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><br>    <SPAN style="color:#00007F">If</SPAN> RegEx <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> RegEx = CreateObject("VBScript.RegExp")<br>        <SPAN style="color:#00007F">With</SPAN> RegEx<br>            .Global = <SPAN style="color:#00007F">True</SPAN><br>            .Pattern = "\b[^ ]{2,}\b"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    CountWords = RegEx.Execute(s).Count<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

Excel Workbook
	B	C
2	cheap t shirts	2
3	t shirts	1
4	long and wide trousers	4
5	a	0
6	John J F K Smith	2
7		0
8	Sophie Rhys-Jones	2
Count Words

 

[Karthik-Excelsior]

B.R.I.L.L.L.L.I.A.N.T Peter!!! That helps.

However, pardon me for being greedy - can we achieve the same results without the help of VBA? I mean, with the already existing set of excel formulae? If not, I'll definitely go by this one!

 

[Peter_SSs]

can we achieve the same results without the help of VBA? I mean, with the already existing set of excel formulae?

I couldn't think of a way, which is why I went with the UDF. Perhaps somebody else will have a different suggestion.