So, the first step is going to be to calculate the number of characters in the cell.
We’ll do that with the Length orLEN function.
And then, we need to figure out the number of characters without spaces.
To get the number of characters without spaces we need to strip the spaces out.
I’ll useSUBSTITUTE, and we’ll point at this text here.
We’ll look for spaces and replace that with “nothing.”
That gives us the string withoutanyspaces.
Now we can build our little formula here that counts words.
If the spacing is not normal, then we’ll get an incorrect word count.
So to fix that, I’m going to strip out the extra spaces.
We’ll insert a row, and I’m going to use theTRIM functionto strip the spaces out.
you might see over there that the extra spaces have been removed.
Let’s go ahead and adjust our formula.
So instead of using the original reference to C5, we’re going to use C6.
Now we get the correct word count.
OK, let’s make this all-in-one.
To do that, I’m going to copy this formula up here, and put it down here.
And we’ll come in here and we’re just going to replace the references one by one.
So C6 is first.
Now looking at it we see B6, and B6 also contains a formula.
So I’m going to copy the formula in B6 over here.
Come back down here, where B6 is, and paste it.
We’re just going to go step by step this way.
Now we want to replace C7, right there.
So we’ll get C7, copy it, and put it down here.
We just have one more reference because B7 is also a formula.
So, come over here and copy the formula in B7, and come down here, and paste.
That’s our all-in-one formula.
I can take this now, and copy it, then put it down here.
I’m going to fix the references, so that they go the right cell.
