Construct Mega-Formulas

Mega-formulas-a formula within a formula, within a formula-are enough to send even the most seasoned Excel veteran running for the hills. With a little forethought and by working step by step toward the formula you need, however, you can tame those complex mega-formulas without fear.

Does the very thought of having to make sense of, let alone construct, nested formulas fill you with dread? Some of those cells, so chock-full of complex functional gobbledygook, make us feel a little faint too. But with a little forethought and a step-by-step approach, you'll be creating mega-formulas without fear. And maybe, just maybe, you'll even be able to read and understand them again later.

The trick is to build up your formulas, bit by bit, using Excel's standardfunctions. Use one function per cell, obtaining individually manageable results, and then nest them together to yield the result you need. Here's an example of such a process in action.

Say you've been given a long list of people's names, each consisting of first, middle, and last names-one full name per cell. Your job is to write a formula in the adjacent column to extract only the person's last name.

What you're after, then, is the start of the last name-the third word-in the cell. Actually, what you'll be looking for is the position of the second space character in the cell. Excel has no standard built-in function to automatically locate the second space character in a cell, but you can bring the FIND function to bear in such a way that it does what you need it to do.

Type the name David John Hawley (or any three-word name) into cell A1. In cell C1, enter this function:

=FIND(" ",A1)

The FIND function finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text from the first character of within_text.

Here is the syntax:

=find(find_text, within_text, start_num)

This will find the starting position of the first space character in cell A1 as you have told it to find " " (a space) in cell A1. In the case of Mikey Rim, it will return a value of 6. But it's the second space you're after, not the first. What you'll do is use the number returned by the formula in C1 as the starting point for another FIND function in search for the second space character. So, in cell C2, enter the following:

=FIND(" ",A1,C1+1)

Notice you've passed the FIND function a third argument this time, the initial position found by C1 (6, in this example), plus 1; this will serve as the starting point for the FIND function to find a space. The value returned will be the position of the second space character.

With that in hand, you want the next function to grab all characters thereafter until the end of the string of text. Use the MID function, which is designed to extract a range of characters from a string. In cell C3, enter the following:

=MID(A1,C2+1,256)

The MID function returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify. Here is its syntax:

MID(text, start_num, num_chars)

This tells the MID function to extract 256 characters from cell A1, starting with the first character after the second space in the string of text. You used 256 simply to ensure that regardless of the length (assuming it's less than 256 characters), you get the person's entire last name. With all the parts in hand, it's time to build out the whole thing: a nested formula you'd have cringed at just a fewminutes ago. Basically, you simply replace all cell references (except A1) within the functions with the formula in those cells. You do this via the use of cut and paste working within the Formula bar.

Click cell C2, and in the Formula bar, highlight the function and copy the entire FIND function except for the =, like this:

FIND(" ",A1,C1+1)

Press Enter to leave the cell, which will place you into cell C3. With cell C3 selected, in the Formula bar, highlight the reference to cell C2 and paste the FIND function (Ctrl-V) that you just copied in its place. Press Enter. Your function in cell C3 should now be as follows:

=MID(A1,FIND(" ",A1,C1+1)+1,256)

Nowyou need to replace the reference to cell C1 with the function that resides in cell C1. Select cell C1, highlight the formula from the Formula bar, omitting the =, click Copy, then press Enter twice to get back to cell C3. While in cell C3, highlight C1 in the Formula bar and paste the FIND function you just copied. Press Enter.

Now all you need to do is cut cell C3 and paste it into cell B1, then delete the formulas left over in cells C1 and C2. You should nowend up with a final formula like this:

=MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,256)

Following this concept, you should be able to see how you can build megaformulas using a variety of Excel's functions. All you need to do is first plan a way that you will achieve it and then use individual cells to obtain the results needed. Finally, replace all cell references with the functions that are housed within them.

In pre-2007 versions, if you have more than seven levels of nested functions, you'll also want to use the INDIRECT function. Excel 2007 allows up to 64 levels of nested functions.