I'm using Excel 2007 - Data is in column A

I have a data set that I am trying to import. The first column really
should be 5 separate columns, but has been created as one string. I need to
separate into 5 columns. the data looks like this:

LastName, FirstName MI. Position Team

LastName and FirstName can be ulimited characters
MI can be 1 or 2 characters followed by a period
Position is 1 or 2 characters
Team is 3 characters

I attempted to use Text to Columns, but the problem is that the middile
initial does not always exist. If I can get the middle initial to a separate
column, I can use text to columns for the rest. I would prefer to just run
one macro to take care of the whole thing since I will have a different file
each year that has the same data structure. Sorry if I didn't provide enough
info. Thanks in advance.