Microsoft Office Tutorials and References
In Depth Information
The three test workbooks that I used are available at this topic's website. The filenames
are time test intermediate.xlsx, time test megaformula.xlsx, and time test named mega-
formula.xlsx. To perform your own time tests, change the name in cell A1 and start your
stopwatch when you press Enter. Keep your eye on the status bar, which indicates
when the calculation is finished.
Using a megaformula to return a string's last space charac-
As previously noted, the “remove middle name” example presented earlier contains a flaw: To identify the last
name, the formula searches for the second space character. A better solution is to search for the last space char-
acter. Unfortunately, Excel doesn't provide any simple way to locate the position of the first occurrence of a
character from the end of a string. The example in this section solves that problem and describes a way to de-
termine the position of the first occurrence of a specific character going backward from the end of a text string.
This technique involves arrays, so you might want to review the material in Part IV to fa-
miliarize yourself with this topic.
This example describes how to create a megaformula that returns the character position of the last space char-
acter in a string. You can, of course, modify the formula to work with any other character.
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: