Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to build a complex formula step by step

Tags 
Summary 
Some formulas you see in Excel can be extraordinarily complex. But all complex formulas are built from simple steps. In this video, we built a more complex formula step by step.
Video Transcript 

When you look at a complex formula in Excel, you may be completely baffled at first glance.  But all complex formulas are just small steps added together.

Let me show you an example.

Here we have a list of names. We want to pull out the first name from the full name.

There's an Excel function called LEFT that will extract text from the left, so let's start there.

Rather then explain how LEFT works, let's just try it to see what it does.

Once I open the parentheses, Excel will tell us what we need to provide. With LEFT, we need to supply text, which is required, and something called num_chars, which is optional.

Let's just throw our names into the function and leave out the num_chars to see what happens.

What we get is the first character. So that's a start.

Now let's add in a value for num_chars. This will change for each name, but to keep things simple, I'll just hard-code the number 5.

When I copy that formula down, I get the first five characters of each name.

So, at this point, you can see what the basic challenge is. We need to figure out a way to give the LEFT function the correct number of characters to extract for each name.

To do this, we can use another function called FIND.

FIND takes two required arguments: the text we're looking for (a space character), and the text we're looking within -- in this case, we'll use an address for the cell that contains the name.

The last argument is optional, so we'll leave it out.

You can see that FIND returns a number. And this number represents the position of the space character in the name.

We can use this number in the LEFT function for num_chars.

When I copy it down, we get a list of first names.

So, this looks right, but actually there's a problem. LEFT extracts all characters up to and including the number you give it. That means we actually have a space at the end of all those first names.

To get rid of the space, I just need to make one simple change.

I need to subtract 1 from the result we get from FiND. That way, we won't pick up the space.

[demo]

Finally, I'll create a combined formula in the last column. The final formula looks more complex, but really, it's just just the LEFT function, with a little fancy footwork using FIND to get figure out the number of characters to extract.

This is a simple example, but the idea is the same for all complex formulas.

Start with the big picture, and work in small steps to get the result you need.

Author 
Dave Bruns

Related shortcuts

CtrlEnter
Return