Microsoft Office Tutorials and References
In Depth Information
Megaformula Examples
This formula essentially reverses the integers generated in column B.
The range D1:D12 contains the following array formula:
This formula uses the MID function to extract the individual characters in cell A1. The MID function
uses the array in C1:C12 as its second argument. The result is an array of the name’s characters in
reverse order.
The formula in cell E1 is as follows:
=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 20-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
This formula returns the character position of the last space in the string.
You may wonder how all of 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 of 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:
Search JabSto ::

Custom Search