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

If you're still not clear about this process, take a look at the step-by-step procedure:

1.
Examine the formula in H1. This formula contains two cell references (F1 and G1):