Microsoft Office Tutorials and References
In Depth Information
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. Then select the
formula text (without the equal sign) by pressing Shift+Home, followed by Shift+ → . Then press
Ctrl+C to copy the selected text to the Clipboard. Press Esc to cancel cell editing. Then, 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 correctly. If the formula returns a different result after you paste the formula text,
press Ctrl+Z to undo the paste. Insert parentheses around the formula you want to copy and
paste it into the megaformula — it should then calculate correctly.
This section contains three additional examples of megaformulas. These examples provide a
thorough introduction 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, determining 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 20-2. Suppose you
have a worksheet 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 formulas.