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

Search JabSto ::

Custom Search