Microsoft Office Tutorials and References

In Depth Information

**Megaformula Examples**

Because an array formula replaced the reference in cell F1, you must now enter the

formula in F1 as an array formula (enter by pressing Ctrl+Shift+Enter).

3.
The formula in cell F1 contains a reference to C1:C12, which also contains an array formula.

Replace the reference to C1:C12 with the array formula in C1:C12 to get this array formula

in cell F1:

{=LEN(A1)+1–MATCH(“ “,MID(A1,LEN(A1)+1–B1:B12,1),0)}

4.
Replace the reference to B1:B12 with the array formula in B1:B12. The result is

{=LEN(A1)+1–MATCH(“ “,MID(A1,LEN(A1)+1–ROW(INDIRECT

(“1:”&LEN(A1))),1),0)}

Now the array formula in cell F1 refers only to cell A1, which is exactly what you want. The

megaformula does the job, and you can delete all the intermediate formulas.

Although you use a 12-digit value and arrays stored in 12-row ranges to create the

formula, the final formula does not use any of these range references. Consequently, the

megaformula works with text of any length.

Putting the megaformula to work

Figure 20-5 shows a worksheet with names in column A. Column B contains the megaformula

developed in the previous section. Column C contains a formula that extracts the characters

beginning after the last space, which represents the last name of the name in column A.

Figure 20-5:
Column B contains a megaformula that returns the character position of the last space of the

name in column A.

Cell C1, for example, contains this formula:

=RIGHT(A1,LEN(A1)–B1)