The step-by-step procedure

If you’re still not clear about this process, take a look at the step-by-step procedure:

1. Examine the formula in H1. This formula contains two cell references (F1 and G1):

   =F1&" "&G1

2. Activate cell G1 and copy the contents of the formula (without the equal sign) to the Clipboard.

3. Activate cell H1 and replace the reference to cell G1 with the Clipboard contents.

   Now cell H1 contains the following formula:

   =F1&" "&RIGHT(B1,LEN(B1)–E1)

4. Activate cell F1 and copy the contents of the formula (without the equal sign) to the Clipboard.

5. Activate cell H1 and replace the reference to cell F1 with the Clipboard contents.

   Now the formula in cell H1 is as follows:

   =LEFT(B1,C1–1)&" "&RIGHT(B1,LEN(B1)–E1)

6. Cell H1 contains references to three cells (B1, C1, and E1).

   The formulas in those cells will replace each of the three references.

7. Replace the reference to cell E1 with the formula in cell E1. The result is

   =LEFT(B1,C1–1)&" "&RIGHT(B1,LEN(B1)–IFERROR(D1,C1))

8. Replace the reference to cell D1 with the formula in cell D1.

   The formula now looks like this:

   =LEFT(B1,C1–1)&" "&RIGHT(B1,LEN(B1)–IFERROR(FIND(" ",B1,C1+1),C1))

9. The formula has three references to cell C1. Replace all three of those references to cell C1 with the formula contained in cell C1.

   The formula in cell H1 is as follows:

   =LEFT(B1,FIND(" ",B1)–1)&" "&RIGHT(B1,LEN(B1)–IFERROR
   (FIND(" ",B1,FIND(" ",B1)+1),FIND(" ",B1)))

10. Finally, replace the seven references to cell B1 with the formula in cell B1. The result is

    =LEFT(TRIM(A1),FIND(" ",TRIM(A1))–1)&" "&RIGHT
    (TRIM(A1),LEN(TRIM(A1))–IFERROR(FIND(" ",TRIM(A1),
    FIND(" ",TRIM(A1))+1),FIND(" ",TRIM(A1))))

Notice that the formula in cell H1 now contains references only to cell A1. The megaformula is complete, and it performs exactly the same tasks as all the intermediate formulas (which you can now delete).

After you create a megaformula, you can create a name for it to simplify using the formula. Here’s an example:

1. Copy the megaformula text to the Clipboard.

   In this example, the megaformula refers to cell A1.

2. Activate cell B1, which is the cell to the right of the cell referenced in the megaformula.
3. Choose Formulas ➜ Defined Names ➜ Define Name to display the New Name dialog box.
4. In the Name field, type NoMiddleName.
5. Activate the Refers To field and press Ctrl+V to paste the megaformula text.
6. Click OK to close the New Name dialog box.

After performing these steps and creating the named formula, you can enter the following formula, and it will return the result using the cell directly to the left:

=NoMiddleName

If you enter this formula in cell K8, it displays the name in cell J8, with no middle name.

This megaformula uses the IFERROR function, so it will not work with versions prior to Excel 2007. A comparable formula that’s compatible with previous versions follows:

=LEFT(TRIM(A1),FIND(" ",TRIM(A1),1)–1)&" "&RIGHT
(TRIM(A1),LEN(TRIM(A1))–IF(ISERROR(FIND(" ",TRIM(A1),
FIND(" ",TRIM(A1),1)+1)),FIND(" ",TRIM(A1),1),FIND(" ",TRIM
(A1),FIND(" ",TRIM(A1),1)+1)))

Comparing speed and efficiency

Because a megaformula is so complex, you may think that using one slows down recalculation. Actually, that’s not the case. As a test, we created three workbooks (each with 175,000 names): one that used six intermediate formulas, one that used a megaformula, and one that used a named megaformula. We compared the results in terms of calculation time and file size; see Table 21.3.

Of course, the actual results will vary depending on your system’s processor speed.

As you can see, using a megaformula (or a named megaformula) in this case resulted in slightly faster recalculations as well as a much smaller workbook.

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 21.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)))}

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}

This formula 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 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 follows:

=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 this:

   =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)}

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.

Putting the megaformula to work

Figure 21.5 shows a worksheet with names in column A. Column B contains the megaformula developed in the previous section. Column C contains a formula that extracts the characters beginning after the last space, which represents the last name of the name in column A.

Cell C1, for example, contains this formula:

=RIGHT(A1,LEN(A1)–B1)

If you like, you can eliminate the formulas in column B and create a specialized formula that returns the last name. To do so, substitute the formula in cell B1 for the reference to cell B1 in the formula. The result is the following array formula:

{=RIGHT(A1,LEN(A1)–(LEN(A1)+1–MATCH(" ",MID(A1,LEN(A1)+
1–ROW(INDIRECT("1:"&LEN(A1))),1),0)))}

The basic formulas

Figure 21.6 shows a worksheet set up to analyze a credit card number and determine its validity. This workbook uses quite a few formulas to make the determination.

In this worksheet, the credit card number is entered in cell F1, with no spaces or hyphens. The formula in cell F2 follows. This formula appends leading zeros, if necessary, to make the card number exactly 16 digits long. The other formulas use the string in cell F2.

=REPT("0",16–LEN(F1))&F1

Column A contains a series of integers from 1 to 16, each representing the digit positions of the credit card.

Column B contains formulas that extract each digit from cell F2. For example, the formula in cell B5 is as follows:

=MID($F$2,A5,1)

Column C contains the multipliers for each digit: alternating 2s and 1s.

Column D contains formulas that multiply the digit in column B by the multiplier in column C. For example, the formula in cell D5 is

=B5*C5

Column E contains formulas that sum the digits displayed in column D. A single digit value in column D is returned directly. For two-digit values, the sum of the digits is displayed in column E. For example, if column D displays 12, the formula in column E returns 3: that is, 1 + 2. The formula that accomplishes this is as follows:

=INT((D5/10)+MOD((D5),10))

Cell E21 contains a simple SUM formula to add the values in column E:

=SUM(E5:E20)

The formula in cell G1, which follows, calculates the remainder when cell E21 is divided by 10. If the remainder is 0, the card number is valid, and the formula displays VALID. Otherwise, the formula displays INVALID.

=IF(MOD(E21,10)=0,"VALID","INVALID")

Convert to array formulas

The megaformula that performs all these calculations will be an array formula because the intermediary formulas occupy multiple rows.

First, you need to convert all the formulas to array formulas. Note that columns A and C consist of values, not formulas. To use the values in a megaformula, they must be generated by formulas—more specifically, array formulas.

Enter the following array formula into the range A5:A20. This array formula returns a series of 16 consecutive integers:

{=ROW(INDIRECT("1:16"))}

1. For column B, select B5:B20 and enter the following array formula, which extracts the digits from the credit card number:

   {=MID($F$2,A5:A20,1)}

2. Column C requires an array formula that generates alternating values of 2 and 1.

   Such a formula, entered into the range C5:C20, is shown here:

   {=(MOD(ROW(INDIRECT("1:16")),2)+1)}

3. For column D, select D5:D20 and enter the following array formula:

   {=B5:B20*C5:C20}

4. Select E5:E20 and enter this array formula:

   {=INT((D5:D20/10)+MOD((D5:D20),10))}

Now the worksheet contains five columns of 16 rows but only five actual formulas (which are multicell array formulas).

Build the megaformula

To create the megaformula for this task, start with cell G1, which is the cell that has the final result. The original formula in G1 is

=IF(MOD(E21,10)=0,"VALID","INVALID")

1. Replace the reference to cell E21 with the formula in E21.

   Doing so results in the following formula in cell G1:

   =IF(MOD(SUM(E5:E20),10)=0,"VALID","INVALID")

2. Replace the reference to E5:E20 with the array formula contained in that range. Now the formula becomes an array formula, so you must enter it by pressing Ctrl+Shift+Enter.

   After the replacement, the formula in G1 is as follows:

   {=IF(MOD(SUM(INT((D5:D20/10)+MOD((D5:D20),10))),10)=0,
   "VALID","INVALID")}

3. Replace the two references to range D5:D20 with the array formula contained in D5:20.

   Doing so results in the following array formula in cell G1:

   {=IF(MOD(SUM(INT((B5:B20*C5:C20/10)+MOD((B5:B20*C5:C20),
   10))),10)=0,"VALID","INVALID")}

4. Replace the references to cell C5:C20 with the array formula in C5:C20.

   Note that you must have a set of parentheses around the copied formula text. The result is as follows:

   {=IF(MOD(SUM(INT((B5:B20*(MOD(ROW(INDIRECT("1:16")),
   2)+1)/10)+MOD((B5:B20*(MOD(ROW(INDIRECT("1:16")),2)+1)),
   10))),10)=0,"VALID","INVALID")}

5. Replacing the references to B5:B20 with the array formula contained in B5:B20 yields the following:

   {=IF(MOD(SUM(INT((MID($F$2,A5:A20,1)*(MOD(ROW(INDIRECT
   ("1:16")),2)+1)/10)+MOD((MID($F$2,A5:A20,1)*(MOD(ROW
   (INDIRECT("1:16")),2)+1)),10))),10)
   =0,"VALID","INVALID")}

6. Substitute the array formula in range A5:A20 for the references to that range.

   The resulting array formula is as follows:

   {=IF(MOD(SUM(INT((MID($F$2,ROW(INDIRECT("1:16")),1)*(MOD(ROW
   (INDIRECT("1:16")),2)+1)/10)+MOD((MID($F$2,ROW(INDIRECT
   ("1:16")),1)*(MOD(ROW(INDIRECT("1:16")),2)+1)),10))),10)=0,
   "VALID","INVALID")}

7. Substitute the formula in cell F2 for the two references to cell F2.

   After making the substitutions, the formula is as follows:

   {=IF(MOD(SUM(INT((MID(REPT("0",16–LEN(F1))&F1,
   ROW(INDIRECT("1:16")),1)*(MOD(ROW(INDIRECT("1:16")),2)+1)/
   10)+MOD((MID(REPT("0",16–LEN(F1))&F1,ROW(INDIRECT("1:16")),
   1)*(MOD(ROW(INDIRECT("1:16")),2)+1)),10))),10)=0,"VALID",
   "INVALID")}

You can delete the now superfluous intermediate formulas. The final megaformula, a mere 229 characters in length, does the work of 51 intermediary formulas!

Figure 21.7 shows this formula at work.