Formula for counting number of words in a cell

WPO-Designory

Hello,

I am trying to figure out how to create a formula to count the number of words in a cell.  Can anyone help? I know how to do this in excel and Google Sheets, but Smartsheet is not able to parse the excel formula as is. The reason I need to do this is we are creating answers for voice search queries and the optimal length is 29 words or less.  Thank you!

J. Craig Williams

My Assumptions:

1. Words are separated by one and only one space.

2. There is at least one word.

3. My column containing the text is [Text Col]

The formula:

=LEN([Text Col]@row) - LEN(SUBSTITUTE([Text Col]@row, " ", "")) + 1

This counts the total length of the string and the total length of the string without spaces and then adds 1.

I hope this gives you what you need.

Craig