I got this to work for me by searching for the blanks in between names
within an entry. If the entry contains two blanks, I assumed the
characters between the blanks were either a middle name or an initial.

If your data is in column A1, for instance "Jo Ann Smith", you
indicated you need the last name in column B, the first name in C, and
the Middle Initial or Middle Name in column D.

In B1, enter this formula: it returns the letters after the last " "
(space) in the entry.
=IF(ISERROR(FIND(" ",A1,FIND(" ",A1,1)+1)),MID(A1,FIND("
",A1,1)+1,LEN(A1)),MID(A1,FIND(" ",A1,FIND(" ",A1,1)+1)+1,LEN(A1)))

In C1, enter this formula to find the first name (the letters before
the first space):
=MID(A1,1,FIND(" ",A1,1)-1)

In D1, enter this formula. It returns the letters between two spaces if
two spaces exist:
=IF(ISERROR(FIND(" ",A1,FIND(" ",A1,1)+1)),"",MID(A1,FIND("
",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1))

Note that you may still need to review your list for entries with "Sr."
or "Jr", since these formulas will treat those titles as last names.