CODEWITHSUNDEEP
google-sheets
Amin Shah Gilani
Amin Shah Gilani

Reputation: 9826

How do I count the number of words in a specific cell in Google Sheets?

The objective is to show a word-count in a cell next to a cell containing a paragraph, like so:

|                              text                                      |   word_count |
|------------------------------------------------------------------------|------------  |
|   Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut ut.      |     120      |
|   Suspendisse a lacus elementum, auctor lorem et, convallis quam.      |              |
|   Mauris facilisis sollicitudin orci, sed auctor dui blandit sit amet. |              |
|   Interdum et malesuada fames ac ante ipsum primis in faucibus.        |              |
|   Nunc ut lacinia mauris. Pellentesque lobortis pretium convallis.     |              |
|   Cras in condimentum elit. Integer pellentesque nibh diam.            |              |
|   Praesent a erat molestie purus luctus malesuada volutpat.            |              |
|   Lorem ipsum dolor sit amet, consectetur adipiscing elit.             |              |
|   Suspendisse et lorem non neque sodales viverra et risus..            |              |               
|   Pellentesque tincidunt rhoncus enim sit amet tempor.                 |              |             
|   Pellentesque non leo non purus eleifend dapibus sit.                 |              |       
|   Sed fermentum lectus vel dapibus euismod. Aenean ante                |              |               
|   nisl, sagittis ut mauris sed, congue imperdiet lectus. Nam           |              |          
|   rutrum condimentum sapien a feugiat  amet a orci.                    |              |

Valid words are:

What formula do I enter in the word_count cell?

Work done so far:

Just this: =COUNTA(SPLIT(A2, ” “))

Upvotes: 1

Views: 91

Answers (2)

JPV
JPV

Reputation: 27262

Maybe you can also try:

=sum(ArrayFormula(--(len(split(regexreplace(A1, "[^A-Za-z\-\s]"," "), " "))>2)))

where 2 is the minimum length of the word.

Upvotes: 1

Dang Nguyen
Dang Nguyen

Reputation: 1209

I came up with an (ugly) solution but at least it works I think

=COUNTA(SPLIT(REGEXREPLACE(A1, "([^\s].{2}[^\s]*)|([^\s].*$)", "randomToken")," "))

Explain: with data a asdad sadad dasdad asdad

REGEXREPLACE() will check all matching pattern ([^\s].{2}[^\s]*)|([^\s].*$) and replace it with randomToken(you can put whatever here)

([^\s].{2}[^\s]*)|([^\s].*$) match 3 any character and after that will stop if meet a space (Note that the minimum character allow is put inside the regex as n-1)

The result after REGEXREPLACE is randomToken randomToken randomToken randomToken

Then just split it and counta, we will have the result.

Hope it can help

Upvotes: 1

Related Questions