Microsoft Office Tutorials and References
In Depth Information
C1
=FIND(“ “,B1)
Locates the first space
D1
=FIND(“ “,B1,C1+1)
Locates the second space, if any
E1
=IFERROR(D1,C1)
Uses the first space if no second space exists
F1
=LEFT(B1,C1–1)
Extracts the first name
G1
=RIGHT(B1,LEN(B1)–E1) Extracts the last name
H1
=F1&” “&G1
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:
=IF(ISERROR(D1),C1,D1)
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.
With a bit of work, you can eliminate all the intermediate formulas and replace them with a single megafor-
mula. You do so by creating all the intermediate formulas and then editing the final result formula (in this case,
the formula in column H) by replacing each cell reference with a copy of the formula in the cell referred to. For-
tunately, you can use the Clipboard to copy and paste. (See the sidebar, “Copying text from a formula,” earlier
in this chapter.) Keep repeating this process until cell H1 contains nothing but references to cell A1. You end up
with the following megaformula in one cell:
=LEFT(TRIM(A1),FIND(“ “,TRIM(A1))–1)&” “&RIGHT
(TRIM(A1),LEN(TRIM(A1))–IFERROR(FIND(“ “,TRIM(A1),
FIND(“ “,TRIM(A1))+1),FIND(“ “,TRIM(A1))))
When you're satisfied that the megaformula works, you can delete the columns that hold the intermediate for-
mulas because they are no longer used.
The step-by-step procedure