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

If you’re still not clear about this process, take a look at 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))