Microsoft Office Tutorials and References

In Depth Information

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

mula, 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 21-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 21-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)

If you like, you can eliminate the formulas in column B and create a specialized formula that returns the last

name. To do so, substitute the formula in cell B1 for the reference to cell B1 in the formula. The result is the

following array formula:

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

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