Microsoft Office Tutorials and References
In Depth Information
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
“,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
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
In fact, I wrote such a function to compare it with intermediate formulas and megaformulas. The
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
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 .