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




Custom Search