Get Middle Name

=TRIM(MID(A1,SEARCH(" ",A1),SEARCH(" ",A1,SEARCH(" ",A1)+1)-SEARCH(" ",A1)))

This will return #VALUE if no middle name is present so you can also try this (it will return a blank if no middle name is present)

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,"",TRIM(MID(A1,SEARCH(" ",A1),SEARCH(" ",A1,SEARCH(" ",A1)+1)-SEARCH(" ",A1))))

Here's another formula by Andy Hollandbeck.

=IF((ISERR(FIND(" ",SUBSTITUTE(A1," ","",1)))),"",LEFT(REPLACE(A1,1,FIND(" ",A1),""),FIND(" ",REPLACE(A1,1,FIND(" ",A1),""))))