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.

Sorry for late respond, just logged back in.

I have checked the code, it works perfectly, many thanks!
Trying to play around.
What if i want to add more data: example: a,b,c,d,e,f instead of a,b,c,d

Thank you.

Thank you!

anyway, is the code only work single word data ( non space ) , i tried to change the data into sentence, somehow not work
example:

a:this is text b:3 c:this is text2 d:text3 text4