complex split string (delimit-text to columns)

Hi i need a function to split names from a single column to many.

We have a column where 2 or more party names may be in 1 cell, like "John Smith and Jane Smith".

Names can also be like "John A. Smith" or "John A. Smith and Mary Smith"

the end result would be columns on 1 row that look like
A= (source data column)
B= JOHN A.
C= SMITH
D= MARY
E= SMITH

when i do this manually, i do a find/replace on " and ", change to a PIPE |,
then i used data/text to columns/set delimiter to | and split so i now have 2 cols with a full party name in each column b and c. then i add 3 or 4 columns between those 2.
then i delimit spaces on each of the combined names columns.
then i concat fname+mi columns back, then copy/paste values over the formulas and clean up the columns to reorder..

as you can see, many manual steps so would be time consuming and confusing to my very basic general excel users. these manual splits and concats are alot to ask them to do manually.

i tried recording a macro while i did a simple text to columns, but when i ran it the workbook went crazy.. plus given strict security on our network i think vba code to replicate the above would be best solution.

i'm also uploading a demo worksheet with raw data names as they would appear on my sheet, any help/suggestions would be so much appreciated, thanks!