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:

{=MID(A1,C1:C12,1)}

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

=LEN(A1)+1–E1

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: