Microsoft Office Tutorials and References

In Depth Information

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

8.
Replace the reference to cell D1 with the formula in cell D1.

The formula now looks like this:

=LEFT(B1,C1–1)&” “&RIGHT(B1,LEN(B1)–IFERROR(FIND(“

“,B1,C1+1),C1))

9.
The formula has three references to cell C1. Replace all three of those references to cell C1 with the for-

mula contained in cell C1.

The formula in cell H1 is as follows:

=LEFT(B1,FIND(“ “,B1)–1)&” “&RIGHT(B1,LEN(B1)–IFERROR

(FIND(“ “,B1,FIND(“ “,B1)+1),FIND(“ “,B1)))

10.
Finally, replace the seven references to cell B1 with the formula in cell B1. The result is

=LEFT(TRIM(A1),FIND(“ “,TRIM(A1))–1)&” “&RIGHT

(TRIM(A1),LEN(TRIM(A1))–IFERROR(FIND(“ “,TRIM(A1),

FIND(“ “,TRIM(A1))+1),FIND(“ “,TRIM(A1))))

Notice that the formula in cell H1 now contains references only to cell A1. The megaformula is complete, and it

performs exactly the same tasks as all the intermediate formulas (which you can now delete).

After you create a megaformula, you can create a name for it to simplify using the formula. Here's an example:

1.
Copy the megaformula text to the Clipboard.

In this example, the megaformula refers to cell A1.

2.
Activate cell B1, which is the cell to the right of the cell referenced in the megaformula.

3.
Choose Formulas
⇒
Defined Names
⇒
Define Name to display the New Name dialog box.

4.
In the Name field, type
NoMiddleName
.