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




Custom Search