It seems you could easily do this using the menu Data | Text to
Columns.

The following steps will suffice with the data you show. You may need
to modify them some if the data is not as uniform as you show.

First move the data in column B to D. Split A into 2 columns using
the
comma as delimiter. Then split B into 2 columns using a space
delimiter.
Split D into 4 columns using a space delimiter. Then merge D and E.

Hth,
Merjet