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.

Search JabSto ::

Custom Search