Microsoft Office Tutorials and References

In Depth Information

**Creating Megaformulas**

You can eliminate the 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’re 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

A workbook that contains the intermediate formulas, the megaformula, and the

NOMIDDLE VBA function is available on the companion CD-ROM. The workbook is

named
megaformula.xlsm
.