Creating Megaformulas

Often, spreadsheets require intermediate formulas to produce a desired result. In other words, a formula may depend on other formulas, which in turn depend on other formulas. After you get all these formulas working correctly, it's often possible to eliminate the intermediate formulas and use what I refer to as a single megaformula instead. The advantages? You use fewer cells (less clutter), the file size is smaller, and recalculation may even be a bit faster. The main disadvantage is that the formula may be impossible to decipher or modify.

Here's an example: Imagine a worksheet that has a column with thousands of people's names. And suppose that you've been asked to remove all the middle names and middle initials from the names - but not all the names have a middle name or initial. Editing the cells manually would take hours, and even Excel's Data Data Tools Convert Text to Table command isn't much help. So you opt for a formula-based solution. Although this is not a difficult task, it normally involves several intermediate formulas.

Figure 3-10 shows the results of the more conventional solution, which requires six intermediate formulas shown in Table 3-5. The names are in column A; the end result goes in column H. Columns B through G hold the intermediate formulas.

Figure 3-10: Removing the middle names and initials requires six intermediate formulas.

Table 3-5: INTERMEDIATE FORMULAS WRITTEN IN ROW 2 IN FIGURE 3-9
Open table as spreadsheet
Column	Intermediate Formula	What It Does
B	=TRIM(A2)	Removes excess spaces.
C	=FIND(" ",B2,1)	Locates the first space.
D	=FIND(" ",B2,C2+1)	Locates the second space. Returns #VALUE! if there is no second space.
E	=IF(ISERROR(D2),C2,D2)	Uses the first space if no second space exists.
F	=LEFT(B2,C2)	Extracts the first name.
G	=RIGHT(B2,LEN(B2)-E2)	Extracts the last name.
H	=F2&G2	Concatenates the two names.

You can eliminate the six intermediate formulas by creating a megaformula. You do so by creating all the intermediate formulas and then going back into the final result formula and replacing each cell reference with a copy of the formula in the cell referred to (without the equal sign). Fortunately, you can use the Clipboard to copy and paste. Keep repeating this process until cell H2 contains nothing but references to cell A2. You end up with the following megaformula in one cell :

 =LEFT(TRIM(A2),FIND (" ",TRIM(A2),1))&RIGHT(TRIM(A2),LEN(TRIM(A2))- IF(ISERROR(FIND(" ",TRIM(A2),FIND(" ",TRIM(A2),1)+1)), FIND(" ",TRIM(A2),1),FIND(" ",TRIM(A2),FIND (" ",TRIM(A2),1)+1)))

When you're satisfied that the megaformula is working, you can delete the columns that hold the intermediate formulas because they are no longer used.

The megaformula performs exactly the same tasks as all the intermediate formulas - although it's virtually impossible for anyone to figure out, even the author. If you decide to use megaformulas, make sure that the intermediate formulas are performing correctly before you start building a megaformula. Even better, keep a single copy of the intermediate formulas somewhere in case you discover an error or need to make a change.

Another way to approach this problem is to create a custom worksheet function in VBA. Then you could replace the megaformula with a simple formula, such as

 =NOMIDDLE(A1)

In fact, I wrote such a function to compare it with intermediate formulas and megaformulas. The listing follows .

 Function NOMIDDLE(n) As String     Dim FirstName As String, LastName As String     n = Application.WorksheetFunction.Trim(n)     FirstName = Left(n, InStr(1, n, " "))     LastName = Right(n, Len(n) - InStrRev(n, " "))     NOMIDDLE = FirstName & LastName End Function

CD-ROM

A workbook that contains the intermediate formulas, the megaformula, and the NOMID-DLE VBA function is available on the companion CD-ROM. The workbook is named megaformula.xlsm .

Because a megaformula is so complex, you may think that using one would slow down recalculation. Actually, that's not the case. As a test, I created a worksheet that used a megaformula to process 150,000 names. Then I created another worksheet that used six intermediate formulas. The megaformula version calculated a bit faster, and produced a much smaller file.

The actual results will vary significantly, depending on system speed, amount of memory installed, and the actual formula.

The VBA function was much slower - I abandoned the timed test after 10 minutes. This is fairly typical of VBA functions; they are always slower than built-in Excel functions.