Creating A “Megaformula”

This tip describes how to create what I call a "megaformula" -- a single formula that does the work of several intermediate formulas.

An Example

The goal is to create a formula that returns the string of characters following the final occurrence of a specified character. For example, consider the text string below (which happens to be a URL):

http://spreadsheetpage.com/index.php/tips

Excel does not provide a straightforward way to extract the characters following the final slash character (i.e., "tips") from this string. It is possible, however, do do so by using a number of intermediate formulas. The figure below shows a multi-formula solution. The original text is in cell A1. Formulas in A2:A6 are used to produce the desired result. The formulas are displayed in column B.

Following is a description of the intermediate formulas (which will eventually be combined into a single formula).

1. Count the number of slash characters (Cell A2)
   The formula in cell A2 returns the number of slash characters in cell A1. Excel doesn't provide a direct way to count specific characters in a cell, so this formula is relatively complex.
2. Replace the last slash character with an arbitrary character (Cell A3)
   The formula in A3 uses the SUBSTITUTE function to replace the last slash character (calculated in A2) with a new character. I chose CHAR(1) because there is little chance of this character actually appearing in the original text string.
3. Get the position of the new character (Cell A4)
   The formula in A4 uses the FIND function to determine the position of the new character.
4. Count the number of characters after the new character (Cell A5)
   The formula in A5 subtracts the position of the new character from the length of the original string. The result is the number of characters after the new character.
5. Get the text after the new character (Cell A6)
   The formula in A6 uses the RIGHT function to extract the characters -- the end result.

Combining the Five Formulas Into One

Next, these five formulas will be combined into a single formula.

1. Activate the cell that displays the final result (in this case, cell A6). Notice that it contains a reference to cell A5.
2. Activate cell A5. Press F2 and select the formula text (but omit the initial equal sign), and press Ctrl+C to copy the text. Press Esc.
3. Re-activate cell A6 and paste the copied text to replace the reference to cell A5. The formula in A6 is now:

=RIGHT(A1,LEN(A1)-A4)

4. The formula contains a reference to cell A4, so activate A4 and copy the formula as text. Then replace the reference to cell A4 with the copied formula text. The formula now looks like this:

RIGHT(A1,LEN(A1)-FIND(CHAR(1),A3))

5. Replace the reference to cell A3 with the formula text from cell A3. The formula now looks like this:

=RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),A2)))

6. Replace the reference to cell A2 with the formula text from cell A2. The formula now looks like this:

=RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

The formula now refers only to cell A1, and the intermediate formula are no longer necessary. This single formula does the work of five other formulas.

This general technique can be applied to other situations in which a final result uses several intermediate formulas.

NOTE: You may think that using such a complex formula would cause the worksheet to calculate more slowly. In fact, you may find just the opposite: Using a single formula in place of multiple formulas may speed up recalculation. Any calculation speed differences, however, will probably not be noticeable unless you have thousands of copies of the formula.

Caveat

Keep in mind that a complex formula such as this is virtually impossible to understand. Therefore, use this type of formula only when you are absolutely certain that it works correctly and you are sure that you will never need to modify it in the future. Better yet, keep a copy of those intermediate formulas -- just in case.