Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to build all-in-one formulas

Tags 
Summary 
This video shows you how to build a more complex formula using helper formulas, then bring them all together in a single, all-in-one formula at the end.
Video Transcript 

This video was inspired by Frank Byl of www.powerconcepts.ca, who outlined his approach in a comment here. Thanks, Frank!

So, in this video. were going to build a formula that counts words in a cell. This cell has 8 words in it, and we're going to build a formula step by step that gives us that number 8. And we're going to use helper formulas to do that and when we're finished, we'll take those helper formulas and will combine those into a single all in one formula that we can use anywhere.

So, the first step is going to be to calculate the number of characters in the cell, and we'll do that with Length or LEN function. And then, we need to figure out the number of characters without spaces and 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, and that gives us the string without any spaces. And, you can see that we had 47 characters to start with, and now we have 40. And 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.

And 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, 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. I'm going to 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. And you can see over there that the extra spaces have been removed.

So now let's go ahead and adjust the formula so that instead of using the original reference to C5, we're going to use C6, and now we get the correct word count.

OK, so now 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. And 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 and copy it...put it down here.

And 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...paste. And that's our all-in-one formula.

And I can take this now and, copy it, put it down here. I'm going to fix the references, so that 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.

Author 
Dave Bruns
Thanks for these great tips as I am new to Excel they are invaluable. - Jenny
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course