Extracting First Name, Middle names and Last names

Hello Excel-lent,

A friend recently asked if it was possible to extract first names, middle names and last names from a list of names and after thinking about it, I replied YES!

How is this done?

Let’s assume you have in;

1.             Cell A1 the name “Sorinola Oladapo Augustine” 
2.          And you want to extract the first name in cell B1, middle name in cell C1 and last name in cell D1 as shown in figure 1
3.            In cell B1, type this formula “ =LEFT(A2,FIND(" ",A2)-1) ”, without the double quotes as shown in       figure 2
4.   In cell C1, type this formula “ =IF(LEN(B2&D2)+2>=LEN(A2),"",MID(A2,LEN(B2)+2,LEN(A2)-LEN(B2&D2)-2)) ”, without the double quotes as shown in figure 3
5.    In cell D1, type this formula  “ =RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))) ”, without the double quotes as shown in figure 4
6.          If you have more than one name in column A, e.g A1: A10, copy cells B1: D1 and select cells B2:D10, then paste what you copied from B1:D1
7.           Try is NOW and see, then share with others!

This has saved you a lot of time rather than having to retype the names one by one which off course is open to errors and typos. You don't have to cram the formula, they are text formulas that we have discussed about in the past. See links below;

1. http://excelgist.blogspot.com.ng/2015/07/discover-these-15-extremely-powerful.html

2. http://excelgist.blogspot.com.ng/2015/07/discover-these-15-extremely-powerful_6.html

3. http://excelgist.blogspot.com.ng/2015/07/discover-these-15-extremely-powerful_13.html

Figure 1

Figure 2.

  

Figure 3

Figure 4

Excel-lently yours,

Sorinola Oladapo

07014282477, 07062932708

BB 52E9802D