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)))}
Search JabSto ::




Custom Search