Suppose, you have been given a name in A1 = Roberto Albert Carlos and you need to remove the middle name. Hence, you want to have an answer Roberto Carlos here.
Formula Way –
The formula for the above case would be
=IF(COUNTIF(A1,"* * *"),LEFT(A1,FIND(" ",A1&" "))&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),"")
OR
=IF(COUNTIF(A1,"* * *"),REPLACE(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1),""),"")
Manual Way –
A. Easy Way
1. Select your column.
2. CTRL+H
3. In Find What: box, put one space followed by one * followed by one space. Hence, you need to put " * " without quote.
4. In Replace With: box, put one space. Hence, you need to put " " without quote.
5. Press Replace All.
6. All Middle Names are removed.
B. 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 Roberto Carlos in B1.
2. Same case do for A2 in B2 i.e. if A2 contains abc de fgh, put abc fgh in B2.
2. Select B3 and click Data tab > Flash Fill
Your column B will be filled in with Middle Name removed.