is there a word-count 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)
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
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 multi-select 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 multi-select cells cannot have the same value listed twice, so it will eliminate duplicate words from your text.
Cheers,
GenevieveNeed more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
Check out the Formula Handbook template!