How to categorize data in category-value format in Excel?

Question

excel

I'm trying to do a regression analysis of data that I've parsed from a single site. I was only able to get data from the site in this awkward format:
The data in the 1st column contains information about the data type, separated by a ':' character.
Year:Storey:Area:Condition:Type:Name
The data in the 2nd column contains the actual information corresponding to the data types in the first column, also separated by ':'
2015:3:170:Renovated:TypeB:John
In my Excel spreadsheet about 13,000 records with data in this format. I want to categorize data.
For clarity, I will present a picture with the original format:

I am trying to convert the data to the following format:

I tried to split the data into columns using separators, but the problem is that the number of categories in the record varies. For 1 record there can be 5 categories (data types), and for another 2 data types.
How to categorize data of this format in Excel?

Answer 1

Quite simple and with formulas. Here's what happened:
Now cell by cell:
For Year :
For Storey :
For Area :
For Condition :
For Type :
For Name :
The main thing is that the fields correspond to the desired cells, or change the names of the cells in the formulas.