How to create a complex formula step by step

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 than explain how LEFT works, let's just try it out 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 num_chars to see what happens.

What we get is the first character of each name, so it's a start.

Now let's add in a value for num_chars. This number 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 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 that out.

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

That means we can use the FIND function inside the LEFT function to represent the number of characters we need to extract.

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

So, this looks right, but actually there's a problem. The LEFT function 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.

The final formula looks more complex, but really, it's just the LEFT function, with a little fancy footwork using FIND to figure out the number of characters we need 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.