Microsoft Office Tutorials and References

In Depth Information

**Creating Megaformulas**

Creating Megaformulas

Often, a formula requires intermediate formulas to produce a desired result. In other words, a

formula may depend on other formulas, which in turn depend on other formulas. After you get all

these formulas working correctly, you can often eliminate the intermediate formulas and use

what I refer to as a single
megaformula
instead. The advantages? You use fewer cells (less

clutter), the file size is smaller, and recalculation may even be a bit faster. The main disadvantage is

that the formula may be impossible to decipher or modify.

Here’s an example: Imagine a worksheet that has a column with thousands of people’s names. And

suppose that you’ve been asked to remove all the middle names and middle initials from the names —

but not all the names have a middle name or initial. Editing the cells manually would take hours, and

even Excel’s Data
➜
Data Tools
➜
Text To Columns command isn’t much help. So you opt for a

formulabased solution. Although this task isn’t difficult, it normally involves several intermediate formulas.

Figure 3-10 shows the results of the more conventional solution, which requires six intermediate

formulas shown in Table 3-5. The names are in column A; the end result goes in column H.

Columns B through G hold the intermediate formulas.

Figure 3-10:
Removing the middle names and initials requires intermediate formulas.

Table 3-5:
Intermediate Formulas Written In Row 2 in Figure 3-10

Column

Intermediate Formula

What It Does

B

=TRIM(A2)

Removes excess spaces.

C

=FIND(“ “,B2,1)

Locates the first space.

D

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

Locates the second space. Returns #VALUE! if there is no second

space.

E

=IF(ISERROR(D2),C2,D2)

Uses the first space if no second space exists.

F

=LEFT(B2,C2)

Extracts the first name.

G

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

Extracts the last name.

H

=F2&G2

Concatenates the two names.