While this little Excel/Google Sheets trick is a pretty straightforward hack, it led me to think about how we use our tools, how we stretch the capabilities of our tools, and think outside the box to come to arrive at a solution. Seems like a lot for a formula trick on counting the number of words in a cell to bring to the table. Perhaps I'm looking into it too much. Perhaps it's just me rambling and opining on something meaningless. Or perhaps, it might cause you to stop, think, and reflect for even a minute about something as trivial as counting words in a cell. Come on this journey with me and learn how a stupid formula trick triggered my synapses to fire in a million directions. Link to the Google Sheet with the formula is here.

Video tutorial of the formula to count the number of words in a cell:

https://youtu.be/JRp5BSr5kuI

Why would you want to count words in a cell?

It's a great question. Maybe you need to see how many words are in a paragraph before you submit some online form that only allows you to submit an answer with 150 characters or 50 words or less. I've probably had to count the number of words in a cell a handful of times and it was probably for cleaning data purposes (more on this later). What may be more common is counting the number of characters in a cell to detect anomalies. In any event, the formula you use for counting the number of words in a cell is similar to how you might count the number of characters in a cell. If you want to skip straight to the answer (or doing a search on Google and maybe this will be highlighted in yellow):

=len(A2)-len(substitute(A2," ",""))+1

This formula should work in Excel and Google Sheets and A2 contains the cell with the words you are trying to count. Let's break this down a bit more, because when you break things down that's where the real learning takes place and it may spur other ideas you can incorporate into your spreadsheets.

Counting and substituting stuff

Composability gives formulas some pretty amazing capabilities. Greater than the sum of its parts kind of thing. One their own, the LEN() and SUBSTITUTE() functions do pretty standard things. The LEN function simply counts the number of characters (including spaces) in a cell:

SUBSTITUTE acts as you might expect. Turn all the "A"s in a cell into "X"s. Turn all the 5s into 9s. The first argument is the cell that contains the data, the second argument is what you are searching for in the value to replace, and the third argument is what everything in the second argument should be replaced with. In the example below, We are looking for all the spaces in cell A5 and replacing them with an empty string. Note the syntax here. A space (what we are looking for) is denoted by two double quotes with a space in between: " ". An empty string is two double quotes next to each other with no space in between them: "". The result, in this case, are sentences with no spaces in between them:

Composability is where the magic happens

What happens when you combine the two formulas together? You may be an Excel or Google Sheets guru and have built advanced nested formulas before. When I step back and see how these formulas--when combined--create interesting results that you wouldn't have expected. People say we're just number crunchers and just know when and how to use formulas correctly.

I'd say the composability of formulas is what inspires creativity and makes bui...