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.

Search JabSto ::

Custom Search