Count amount of words that are specific length

user_2735624 0
Viewed 51

Im trying to count amount of words in a cell that are larger or equal to 3 characters.

I've got the simple "counta" working, which is 

=IF(ISBLANK(D2);0;LEN(TRIM(D2))-LEN(SUBSTITUTE(D2;" ";""))+1)

or 

=IF(LEN(D2)=0;0;COUNTA(SPLIT(D2;" "))

)

I know that in regex it would be something simple like this

"\w{3,}"

But I can't come up with a solution to only count the words that are specific length in Sheet syntax. Can someone help me with that, or at least guide me in the right direction.

Thank you in advance

formula google-sheets vba excel
2 Answers

Something like

=SUMPRODUCT(--ARRAYFORMULA(len(SPLIT(D2," "))>=3))
user_62412350

Maybe there's a shorter formula, but this works in Sheets.

=COUNTA(IFERROR(FILTER(SPLIT(D2, " "), LEN(SPLIT(D2, " "))>=3)))
user_13294980
Related