Microsoft Office Tutorials and References

In Depth Information

**Megaformula Examples**

This technique involves arrays, so you might want to review the material in Part IV to

familiarize yourself with this topic.

This example describes how to create a megaformula that returns the character position of the

last
space character
in a string. You can, of course, modify the formula to work with any other

character.

Creating the intermediate formulas

The general plan is to create an array of characters in the string but in reverse order. After that

array is created, you can use the MATCH function to locate the first space character in the array.

Refer to Figure 20-4, which shows the results of the intermediate formulas. Cell A1 contains an

arbitrary name, which happens to use 12 characters. The range B1:B12 contains the following array

formula:

{=ROW(INDIRECT(“1:”&LEN(A1)))}

Figure 20-4:
These intermediate formulas will eventually be converted to a single megaformula.

This example, named
positi
o
n of last space.xlsx
, is available on the

companion CD-ROM.

You enter this multicell array formula into the entire B1:B12 range by selecting the range, typing

the formula, and pressing Ctrl+Shift+Enter. Don’t type the curly brackets. Excel adds the curly

brackets to indicate an array formula. This formula returns an array of 12 consecutive integers.

The range C1:C12 contains the following array formula:

{=LEN(A1)+1–B1:B12}