Microsoft Office Tutorials and References

In Depth Information

**Megaformula Examples**

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

5.
Activate the Refers To field, and press Ctrl+V to paste the megaformula text.

6.
Click OK to close the New Name dialog box.

After performing these steps and creating the named formula, you can enter the following

formula, and it will return the result using the cell directly to the left:

=NoMiddleName

If you enter this formula in cell K8, it displays the name in cell J8, with no middle name.

See Chapter 3 for more information about creating and using named formulas.