Microsoft Office Tutorials and References
In Depth Information
8. Replace the reference to cell D1 with the formula in cell D1.
The formula now looks like this:
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
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
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
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:
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.