Microsoft Office Tutorials and References

In Depth Information

**Creating Megaformulas**

and paste the formulas rather than type them over again. Keep repeating this process until cell H1

contains nothing other than references to cell A1. You end up with the following megaformula in

one cell:

=LEFT(TRIM(A2),FIND(“ “,TRIM(A2),1))&

RIGHT(TRIM(A2),LEN(TRIM(A2))-

IFERROR(FIND(“ “,TRIM(A2),FIND(“ “,TRIM(A2),1)+1),

FIND(“ “,TRIM(A2),1)))

When you’re satisfied that the megaformula works, you can delete the columns that hold the

intermediate formulas because they’re no longer used.

If you’re still not clear about this process, take a look at the step-by-step procedure:

1.
Examine the formula in H2.

This formula contains two cell references (F2 and G2):

=F2&G2

2.
Activate cell G2 and copy the contents of the formula (without the equal sign) to the

Clipboard.

3.
Activate cell H2 and replace the reference to cell G2 with the Clipboard contents.

Now cell H2 contains the following formula:

=F2&RIGHT(B2,LEN(B2)-E2)

4.
Activate cell F2 and copy the contents of the formula (without the equal sign) to the

Clipboard.

5.
Activate cell H2 and replace the reference to cell F2 with the Clipboard contents.

Now the formula in cell H2 is

=LEFT(B2,C2)&RIGHT(B2,LEN(B2)-E2)

6.
Now cell H2 contains references to three cells (B2, C2, and E2).

The formulas in those cells replace each of the references to those cells.

7.
Replace the reference to cell E2 with the formula in E2.

The result is

=LEFT(B2,C2)&RIGHT(B2,LEN(B2)-IFERROR(D2,C2))