Count words having more than one character in a cell

[Admin]

Hi All,

Here is a formula based solution to count words having more than one character in a cell.

If you have data in A2:Ax on Sheet1,

select B2 (It's very important)

Hit CTRL + F3 (to open the Name Manager)

Click on New ;

Name: String

Refers to: =EVALUATE("{"""&SUBSTITUTE(Sheet1!$A2," ",""";""")&"""}")

Now in B2 and copied down,

=INDEX(FREQUENCY(LEN(String),{1}),2)

Hope this helps !

[Rick Rothstein]

Here is a formula based solution to count words having more than one character in a cell.

If you have data in A2:Ax on Sheet1,

select B2 (It's very important)

Hit CTRL + F3 (to open the Name Manager)

Click on New ;

Name: String

Refers to: =EVALUATE("{"""&SUBSTITUTE(Sheet1!$A2," ",""";""")&"""}")

Now in B2 and copied down,

=INDEX(FREQUENCY(LEN(String),{1}),2)

I am pretty sure this simple, direct formula placed in B2 and copied down will do the same thing as what you have posted does...

=1*(LEN(A2)>1)

[Admin]

Rick,

I think you mis-interpreted the sentence. My formula would give you the count of those words only which have more than one character in a cell.

[Rick Rothstein]

Rick,

I think you mis-interpreted the sentence. My formula would give you the count of those words only which have more than one character in a cell.

Yes, you are right... I did mis-interpret your sentence. Sorry for any confusion that may have caused. However, with that said, there is still a direct (although not all that simple) formula available to obtain this count...

=SUMPRODUCT(1*(LEN(TRIM(MID(SUBSTITUTE($A3," ",REPT(" ",999)),ROW(INDIRECT("A1:A999"))*999-998,999)))>1))