is there a wordcount formula?
I have a cell where I need to know how many words there are.
Best Answer

Check the length of the sting, remove all the spaces, check again, the difference + 1 is the word count.
=IF(text@row <> "", LEN(text@row)  LEN(SUBSTITUTE(text@row, " ", "")) + 1, 0)
Answers

Check the length of the sting, remove all the spaces, check again, the difference + 1 is the word count.
=IF(text@row <> "", LEN(text@row)  LEN(SUBSTITUTE(text@row, " ", "")) + 1, 0)

This did not work for me. I got #invalidvalue

Here is the formula I'm using:
=IF(Headline@row <> "", LEN(SUBSTITUTE(Headline@row, "", "")) + 1, 0)
References a column labeled Headline and should give me the # of words, but isn't. Not sure what is wrong.

Hi @Brian R.
Try adding a space between your first "". If there are no spaces, it may not be able to do what you're asking of it (remove spaces)
=IF(Headline@row <> "", LEN(SUBSTITUTE(Headline@row, " ", "")) + 1, 0)
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Hi  I added a space to each of the quotation marks and a value was returned! However, it seems the value isn't correct. I entered "Hello" in the Headline column and it was counted as 6 words, which it is the # of characters. Same for Body Copy column "To be or not to be" is counted as 19 and not 6. I screenshot the sheet, and the formula's for each column. What is incorrect where it's counting characters and not words? So close! Thank you for your help!

Hi @Brian R.
Ah! Thank you for clarifying. Yes, LEN counts characters, not words. Here's more information:
Since Smartsheet does not have a function that directly counts words, I'd use a similar Substitute formula to translate your text into a multiselect cell that has each word "selected" individually:
=IF(Headline@row <> "", SUBSTITUTE(Headline@row, " ", CHAR(10)), "")
Then you can use =COUNTM([Column Reference]@row) to count how many individual values are in that cell. See: COUNTM
Keep in mind that multiselect cells cannot have the same value listed twice, so it will eliminate duplicate words from your text.
Cheers,
GenevieveJoin us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
Check out the Formula Handbook template!