Microsoft Office Tutorials and References
In Depth Information
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
The formulas in row 2 are listed in Table 125-1.
Table 125-1: Intermediate Formulas
What It Does
Removes excess spaces
Locates the first space
Locates the second space, if any
Uses the first space if no second space exists
Extracts the first name
Extracts the last name
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