Microsoft Office Tutorials and References

In Depth Information

**Creating Megaformulas**

Creating Megaformulas

This tip describes a method of combining several intermediate formulas to create a single long

formula (a
megaformula
). If you’ve used Excel for a while, you might have seen some lengthy

formulas that were virtually incomprehensible. Here, you learn how they were created.

The goal is to create a single formula that removes the middle name (for example, Billy Joe

Shaver becomes Billy Shaver). Figure 125-1 shows a worksheet with some names, plus six

columns of intermediate formulas that accomplish the goal. Notice that the formulas aren’t perfect;

they can’t handle a single-word name.

Figure 125-1:
Removing the middle names and initials requires six intermediate formulas — or one

megaformula.

The formulas in row 2 are listed in Table 125-1.

Table 125-1:
Intermediate Formulas

Cell

Intermediate Formula

What It Does

B2

=TRIM(A2)

Removes excess spaces

C2

=FIND(“ “,B2,1)

Locates the first space

=FIND(“ “,B2,C2+1)

D2

Locates the second space, if any

=IFERROR(D2,C2)

E2

Uses the first space if no second space exists

F2

=LEFT(B2,C2)

Extracts the first name

G2

=RIGHT(B2,LEN(B2)-E2)

Extracts the last name

H2

=F2&G2

Concatenates the two names

With a bit of work, you can eliminate all of the intermediate formulas and replace them with a

single megaformula. You do so by creating all the intermediate formulas and then editing the

final result formula (in this case, the formula in column H) by replacing each cell reference with a

copy of the formula in the cell that’s referred to. Fortunately, you can use the Clipboard to copy