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

Search JabSto ::

Custom Search