Microsoft Office Tutorials and References

In Depth Information

**Creating Megaformulas**

8.
Copy the formula from D2 and replace the references to cell D2.

The formula now looks like this:

=LEFT(B2,C2)&RIGHT(B2,LEN(B2)-IFERROR(FIND(“ “,B2,C2+1),C2))

The formula has three references to cell C2.

9.
Replace each of those references with the formula contained in cell C2.

The formula in cell H2 is

=LEFT(B2,FIND(“ “,B2,1))&RIGHT(B2,LEN(B2)-IFERROR(FIND(“ “,B2,FIND

(“ “,B2,1)+1),FIND(“ “,B2,1)))

10.
Finally, replace the seven references to cell B2 with the formula in cell B2.

The result is

=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)))

Notice that the formula in cell H2 now contains references only to cell A2. The megaformula is

complete, and it performs exactly the same tasks as all the intermediate formulas (which you can

now delete).

You can, of course, adapt this technique to your own needs. A nice byproduct is that a single

megaformula often calculates faster than a series of formulas does.

While you’re replacing the cell references with formula text, make sure that the

formula continues to display the correct result after each replacement. In some

situations, you might need to enclose the copied formula in parentheses.