Splitting data from one cell to multiple columns - no fixed length

[beergum]

Hi,

I need help figuring out an easy way to split data entered in 1 cell (separated by spaces - lengths are not fixed) into their own columns. I have about 100k entries to work with.

Sample data layout (All in Column A): Screenshot by Lightshot

I'm looking for a way to split each line into columns based on their entries. The only piece of information with fixed length is the first entry for each set which is 9 characters long. I need all the entries in each set to be their own columns. Each set is separated by the series of --- and |

Any help would be much appreciated.

Thank you!
[TABLE="width: 880"]
<tbody>[TR]
[TD="colspan: 10"][/TD]
[/TR]
[TR]
[TD="colspan: 9"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 9"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 10"][/TD]
[/TR]
[TR]
[TD="colspan: 10"][/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

 

[Stridhan]

Text to columns with blank as separator?

 

[beergum]

Thank you. I tried that but the problem is how I can move the other entries from the set to columns in the same row. Set 1 on the screenshot is from rows 1 - 4. After doing Text to Column, I would need everything on rows 2 - 4 to move to their own columns on row 1.
Also, the data's not entered consistently. Some of them have blanks where there shouldn't be, some of them don't where there should be :|