Microsoft Office Tutorials and References

In Depth Information

=MATCH(“ “,D1:D12,0)

This formula, which is
not
an array formula, uses the MATCH function to return the position of the first space

character in the range D1:D12. In the example shown in Figure 21-4, the formula returns 6, which means that

the first space character is six characters from the end of the text in cell A1.

The formula in cell F1 is

=LEN(A1)+1–E1

This formula returns the character position of the last space in the string.

You may wonder how all these formulas can possibly be combined into a single formula. Keep reading for the

answer.

Creating the megaformula

At this point, cell F1 contains the result that you're looking for — the number that indicates the position of the

last space character. The challenge is consolidating all those intermediate formulas into a single formula. The

goal is to produce a formula that contains only references to cell A1. These steps will get you to that goal:

1.
The formula in cell F1 contains a reference to cell E1. Replace that reference with the text of the formula in

cell E1.

As a result, the formula in cell F1 becomes

=LEN(A1)+1–MATCH(“ “,D1:D12,0)

2.
The formula contains a reference to D1:D12. This range contains a single array formula.

Replacing the reference to D1:D12 with the array formula results in the following array formula in cell F1:

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

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

mula 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.