Microsoft Office Tutorials and References

In Depth Information

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 that 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-7, 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 up these cases.

If you want to know how I created these complex formulas, see Chapter 21 for a discus-

sion of megaformulas.