I know I implied that I wouldn't bother you guys any more, but I just couldn't get it out of my mind. Thanks to my DVR, I solved this one first and watched the Superbowl later.



This solution is for the last name. I arbitrarily used A16 for the work row. Basically, it looks for the 1st space and an optional 2nd space. If the 1st space is alone, it takes the rest of the name starting at 1st space+1, otherwise it takes the rest of the name starting at 2nd space +1.

=(MID(A16,(IFERROR(FIND(" ",A16,(FIND(" ",A16)+1)),FIND(" ",A16)))+1,LEN(A16)))

FWIW, I assume that the full name has been pre-processed to preclude redundant or embedded spaces, per my previous little lecture.

Now I want to solve for the Middle Name, now that you appear to have expanded it from Middle Initial. Obviously if you want to stick with a consistent single-letter middle initial without any punctuation or additional letters, the change in the code will be trivial.

Later.

Hey, I even got comfortable with the ISERROR!

To extract the middle name, work with the string in the full name starting after the 1st space, and try to deliver the number of bytes in the middle name: (position of 2nd space) - (position of 1st space) - 1. If this fails because there is no 2nd space, deliver a 0. This number becomes the third parameter of the MID function.

=MID(A16,FIND(" ",A16)+1,IFERROR((FIND(" ",A16,(FIND(" ",A16)+1))) - (FIND(" ",A16))-1,0))

To extract just the middle initial, look for a second space. If it's there, deliver the first letter of the middle name; otherwise deliver a null.

=IF(ISERROR(FIND(" ",A16,(FIND(" ",A16)+1))),"",(MID(A16,FIND(" ",A16)+1,1)))

Are we having fun yet?