Microsoft Office Tutorials and References

In Depth Information

**Advanced Text Formulas**

The formula that follows is a much shorter way to extract the middle name. This formula is useful

if you use the other formulas to extract the first name and the last name. It assumes that the first

name is in B1 and the last name is in D1.

=IF(LEN(B1&D1)+2>=LEN(A1),””,MID(A1,LEN(B1)+2,LEN(A1)-LEN(B1&D1)-2)

As you can see in Figure 5-6, the formulas work fairly well. There are a few problems, however —

notably names that contain four “words.” But, as I mentioned earlier, you can clean these cases

up manually.

If you want to know how I created these complex formulas, refer to Chapter 20 for a

discussion of megaformulas.

Figure 5-6:
This worksheet uses formulas to extract the first name, middle name (or initial), and last name

from a list of names in column A.

Removing titles from names

You can use the formula that follows to remove four common titles (Mr., Dr., Ms., and Mrs.) from

a name. For example, if cell A1 contains
Mr. Fred Munster,
the formula would return
Fred Munster.

=IF(OR(LEFT(A1,2)={“Mr”,”Dr”,”Ms”}),RIGHT(A1,LEN(A1)-(FIND(“.”,A1)+1)),A1)