Microsoft Office Tutorials and References

In Depth Information

Creating a megaformula essentially involves substituting formula text for cell references in a formula. You per-

form substitutions until the megaformula contains no references to formula cells. At each step along the way,

you can check your work by ensuring that the formula continues to display the same result. In the previous ex-

ample, a few of the steps required parentheses around the copied formula in order to ensure the correct order of

calculation.

Copying text from a formula

Creating megaformulas involves copying formula text and then replacing a cell reference with the copied text. To

copy the contents of a formula, activate the cell and press F2. Select the formula text (without the equal sign) by

pressing Shift+Home, followed by Shift+→. Press Ctrl+C to copy the selected text to the Clipboard and then press

Esc to cancel cell editing. Activate the cell that contains the megaformula and press F2. Use the arrow keys, and

hold down Shift to select the cell reference that you want to replace. Finally, press Ctrl+V to replace the selected

text with the Clipboard contents.

In some cases, you need to insert parentheses around the copied formula text to make the formula calculate cor-

rectly. If the formula returns a different result after you paste the formula text, press Ctrl+Z to undo the paste. In-

sert parentheses around the formula you want to copy and paste it into the megaformula — it should then calcu-

late correctly.

Megaformula Examples

This section contains three additional examples of megaformulas. These examples provide a thorough introduc-

tion to applying the megaformula technique for streamlining a variety of tasks, including cleaning up a list of

names by removing middle names and initials, returning the position of the last space character in a string, de-

termining whether a credit card number is valid, and generating a list of random names.

Using a megaformula to remove middle names

Consider a worksheet with a column of names, like the one shown in Figure 21-2. Suppose you have a work-

sheet with thousands of such names, and you need to remove all the middle names and middle initials from the

names. Editing the cells manually would take hours, and you're not up to writing a VBA macro, so that leaves

using a formula-based solution. Notice that not all the names have a middle name or a middle initial, which

makes the task a bit trickier. Although this is not a difficult task, it normally involves several intermediate for-

mulas.

The Flash Fill feature, introduced in Excel 2013, can handle this task without using for-

mulas. See Chapter 16 for more information about Flash Fill.

Figure 21-3 shows the results of the more conventional solution, which requires six intermediate formulas, as

shown in Table 21-2. The names are in column A; column H displays the end result. Columns B:G hold the in-

termediate formulas.