is there a word-count formula?

steven.nienaber105336

I have a cell where I need to know how many words there are.

Paul H

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)

Paul H

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)

Brian R.

This did not work for me. I got #invalidvalue

Brian R.

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.

Genevieve P.

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)

Brian R.

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!

Screenshot 2024-05-03 at 11.08.15 AM.png

Screenshot 2024-05-03 at 11.08.08 AM.png

Screenshot 2024-05-03 at 11.07.57 AM.png

Screenshot 2024-05-03 at 11.07.33 AM.png

Genevieve P.

Hi @Brian R.

Ah! Thank you for clarifying. Yes, LEN counts characters, not words. Here's more information: https://help.smartsheet.com/function/len

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