Split data into multiple columns and rows based on specific string

Hello

It's been a while from the last visit to this great forum and touch vba things.

I have a file contain 2 columns, ID and data ( a b c d )
the goal is to split the data into multiple column ( ID, a, b, c, d )
how ever the data in column B is sometime not ( a b c d), but repeat data ( a b c d a b c d a b c d)
so they need to split into multiple column and multiple rows based on the ID.

Attached is the example file, i hope i get a hint about the correct vba code.

Thank you all.