How to count words?

How to count words? There is no longer a need to send telegrams, and the need to count words seldom arises. However, this is a nice opportunity to try out how to use the TEXTSPLIT function in combination with some other Excel functions. Who knows, maybe this will be useful to you one day? In honor of Pink Floyd, let’s count the words in the song “Shine On You Crazy Diamond”.

To begin with, let’s find the lyrics on the Internet and put them to cell A2. On this occasion, don’t forget to turn on the Wrap Text option to display text in multiple lines.

To break the text into words use the formula which contains TEXTSPLIT function. In cell B4, write the expression:

=TEXTSPLIT(A2,,” “,TRUE,0)

Logically, the solution would be to count the words we got with this formula. But before that, we need to remove commas and full stops first. Let’s modify the formula:

=TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2,””,” “),”,”,” “),,” “,TRUE,0)

Finally, let’s count the words by nesting this expression inside the COUNTA function:

=COUNTA(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2,””,” “),”,”,” “),,” “,TRUE,0))

We have solution! The song “Shine On You Crazy Diamond” consists of 115 words! Just for fun, we’ll ad this number to the star shape.