Formula for counting number of words in a cell

edited 12/09/19 in Smartsheet Basics


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
    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.