So, the first step is going to be to calculate the number of characters in the cell. We’ll do that with the Length or LEN 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 use SUBSTITUTE, and we’ll point at this text here. We’ll look for spaces and replace that with “nothing.” That gives us the string without any spaces. You can see that we had 47 characters to start with, and now we have 40. Now we can build our little formula here that counts words. We’ll just say: we want to take 47 minus 40, plus 1, and we get 8. This works great, but we do have a problem if we come in here and add a space at the beginning. We’ll get 9 down here, and if I add a space somewhere in the middle, we get 10. You can see that we have a problem. 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 the TRIM function to strip the spaces out. It’s automatic. You can see now, if I drag this down, that we get 47 which is the correct number of characters, once spaces have been normalized. You can 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. We’re going to come up here to C6 and copy the formula that’s in C6 and hit Return. 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. When we copy this down, we get a formula that’s all-in-one that counts the number of words in a cell.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
