Microsoft Office Tutorials and References
In Depth Information
Figure 20-3 shows the results of the more conventional solution, which requires six intermediate
formulas, as shown in Table 20-2. The names are in column A; column H displays the end result.
Columns B-G hold the intermediate formulas.
Figure 20-3: Removing the middle names and initials requires six intermediate formulas.
You can access the workbook for removing middle names and initials on the
companion CD-ROM. The filename is no middle name.xlsx .
Table 20-2: Intermediate Formulas in the First Row of Sheet1 in Figure 20-3
What It Does
Removes excess spaces
Locates the first space
Locates the second space, if any
Uses the first space if no second space exists
Extracts the first name
Extracts the last name
Concatenates the two names
Note that cell E1 uses the IFERROR function, which was introduced in Excel 2007. For
compatibility with earlier versions, use this formula:
Notice that the result isn’t perfect. For example, it will not work if the cell contains only
one name (for example, Enya). And, this method also fails if a name has two middle
names (such as John Jacob Robert Smith). That occurs because the formula simply
searches for the second space character in the name. In this example, the megaformula
returns John Robert Smith. Later in this chapter, I present an array formula method to
identify the last space character in a string.