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)
-
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,
Genevieve
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!