Suppose, you have been given a name in A1 = Roberto Albert Carlos and you need to extract the middle name. We have already talked about extracting first name and last name in following Tips and Tricks. We also talked about extracting initial of a middle name.
Tips & Tricks 22 – Extract First Name from Full Name
Tips & Tricks 23 – Extract Last Name from Full Name
Tips & Tricks 24 – Extract the Initial of Middle Name
Formula Way –
The formula for extracting Middle Name would be –
=IF(COUNTIF(A1,"* * *"),MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)),"")
OR
=IF(COUNTIF(A1,"* * *"),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1)),2),FIND(" ",A1)+1,LEN(A1))),"")
OR
=IF(COUNTIF(A1,"* * *"),LEFT(REPLACE(A1,1,FIND(" ",A1),""),FIND(" ",REPLACE(A1,1,FIND(" ",A1),""))-1))
Manual Way –
A. Flash Fill (Only for Excel 2013) Way – If you data always contains 3 Words
1. Let's say that your first entry, Roberto Albert Carlos is in A1. Put Albert in B1.
2. Select B2 and click Data tab > Flash Fill
Your column B will be filled in with Middle Name.
B. Text to Columns Way – If you data always contains 3 Words
If your list of names is always having 3 names i.e. middle name is always present, we can have a manual way also.
1. Select your column.
2. Data tab > Text to Columns > Delimited should be selected in Step 1 of 3 and Next
3. Check Space in Step 2 of 3 and Next
4. In Step 3 of 3 – Select first column and check "Do not import column (skip)", Select third column and check "Do not import column (skip)" and Finish
5. You have only middle name in your column.
