Suppose, you have a name John Doe Smith and you want to show D as middle initial. Assuming, your data is in A1, you may use following formula
=IF(COUNTIF(A1,"* * *"),MID(A1,FIND(" ",A1)+1,1),"")
If name is of 2 or 1 words, the result will be blank. This works on 3 words name only as middle can be decided only for 3 words name.