Microsoft Office Tutorials and References
In Depth Information
Megaformula Examples
With a bit of work, you can eliminate all 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 referred to. Fortunately, you can use the Clipboard to copy and paste.
(See the sidebar, “Copying text from a formula,” earlier in this chapter.) Keep repeating this
process until cell H1 contains nothing but references to cell A1. You end up with the following
megaformula in one cell:
=LEFT(TRIM(A1),FIND(“ “,TRIM(A1))–1)&” “&RIGHT
(TRIM(A1),LEN(TRIM(A1))–IFERROR(FIND(“ “,TRIM(A1),
FIND(“ “,TRIM(A1))+1),FIND(“ “,TRIM(A1))))
When you’re satisfied that the megaformula works, you can delete the columns that hold the
intermediate formulas because they are no longer used.
The step-by-step procedure
1. Examine the formula in H1. This formula contains two cell references (F1 and G1):
=F1&” “&G1
2. Activate cell G1 and copy the contents of the formula (without the equal sign) to the
Clipboard.
3. Activate cell H1 and replace the reference to cell G1 with the Clipboard contents.
Now cell H1 contains the following formula:
=F1&” “&RIGHT(B1,LEN(B1)–E1)
4. Activate cell F1 and copy the contents of the formula (without the equal sign) to the
Clipboard.
5. Activate cell H1 and replace the reference to cell F1 with the Clipboard contents.
Now the formula in cell H1 is as follows:
=LEFT(B1,C1–1)&” “&RIGHT(B1,LEN(B1)–E1)
6. Now cell H1 contains references to three cells (B1, C1, and E1).
The formulas in those cells will replace each of the three references.
7. Replace the reference to cell E1 with the formula in cell E1. The result is
=LEFT(B1,C1–1)&” “&RIGHT(B1,LEN(B1)–IFERROR(D1,C1))

Search JabSto ::

Custom Search