Hi, this is Wayne again with a topic “Use the LEN and SUBSTITUTE Functions to Make an Excel Formula for Word Count”.
In this Excel video, I’m going to show you how to construct an Excel formula that will count the number of words in a Cell. Let’S get started so Microsoft. Excel does not seem to have a word count option if we go to the review Tab and look at the review ribbon. I may just be missing it, but I don’t see an option to count the number of words in a Cell. Fortunately, we can write a formula to help us do this, so in this spreadsheet, from NASA there are several cells that are descriptions, and I just want to check to make sure that these aren’t getting too wordy they’re, not too long.
Let’S do a word count of each of these, so here in cell J2, I’m just going to click type equals and I’m going to use the Len function and the substitute function to figure out how many words are in this cell, so equals Len. I’Ll put in my left parenthesis and then I’m going to click on Cell I2. Now I could just type I2 here in the formula – that’s okay too, and at this point, because this formula is going to get fairly complicated, I’m going to leave this cell alone and actually move up here to the formula bar whenever I’m working on a formula. That’S more than just uh a few characters I like to move up here and do the work in the formula bar. So so far we have equals Len left parenthesis. I 2 I’m going to close the parentheses and then I’m going to subtract, Len left parenthesis and then I’m just going to type in the substitute function notice what the substitute function does it replaces existing text with new text in a text string so substitute left parenthesis Again, I’m going to select I2 comma and then I’ll put a quotation mark space, another quotation, mark, comma and then I’ll I’ll put two quotation marks one right after the other WR parenthesis, WR parenthesis again and then I’m going to add one now. Why am I going to add one? The reason? Why is because the first word in this paragraph won’t have a space in front of it, and so what we’re doing here in this formula is we’re counting the number of spaces that are in a cell and each time there’s a space that denotes a new word, But again the first word won’t have a space in front of it, so we need to compensate for that by adding one I’m going to tap enter on the keyboard and I’ve calculated the number of words in cell I2 at 66. Now, as you probably know, the good news is, I don’t have to keep writing that same complicated formula over and over.
I can just click on the cell, in which I have my formula and then go down here to the lower right corner, where, where there’s a small green square, and then I can either click and hold the click and drag that down the rest of the spread Sheet, or in this case I could just doubleclick on the fill handle that little green square and it copies the formula all the way down my data set. So, by using this formula and using the Len function and the substitute function, I’ve been able to successfully create a word count for each of these descriptions in my Excel spreadsheet, thanks for watching. I hope you found this tutorial to be helpful. If you did please like follow And subscribe and when you do click the bell and you’ll be notified. When I post another video, if you’d like to support my channel, you can do that a couple of different ways. You could click the thanks button below the video to do a one-time donation. Another option is, you could support me through my patreon account at the $ 1 level or the $ 5 level, or anything in between or more whatever you feel comfortable with and then. Finally, you can support the channel by buying Channel merch t-shirts, mugs, jackets, Etc and you’ll find more information about all of these options.
In the description below the video speaking of patreon, I want to give a quick shout out to my $ 5 patreon supporters. Thank you. So much for the support that you give me and my channel .