Is there a way to separate a column with multi level structure into multiple columns with respective-CodePudding

I have an excel file in the following format, where Project/Task/Subtask are all included in a single column (e.g. AA201 = project, AA201.01 = task 01 under project AA201, and AA201.0101 = subtask 01 under task 01 under project AA201).

Formulas

For Subtask (column I): Identifies all the subtasks based on ID length

=UNIQUE(FILTER(B2:B7, LEN(B2:B7)=10))

Having that, then we can populate the corresponding description (Column J) and drag down the formula:

=IF(I2="","", XLOOKUP(I2,$B$2:$B$7,$C$2:$C$7))

Note: We populate no information as empty string ("") in all columns.

Now for Task (Column G):

=VSTACK(LEFT(FILTER(I2:I20, I2:I20<>""),8), 
  FILTER(LEFT(B2:B7,8), LEN(B2:B7)=8))

We concatenate two arrays via VSTACK, to consider first task information from the subtasks and then tasks itself after.

Having this information populated we can obtain now the corresponding description (Column H) as follow and drag down the formula:

=IF(G2<>"", XLOOKUP(G2, $B$2:$B$7,$C$2:$C$7),"")

and finally the Project column (Column E):

=LET(filter, FILTER(LEFT(B2:B7,5), LEN(B2:B7)=5), 
  VSTACK(XLOOKUP(LEFT(FILTER(G2:G20, G2:G20<>""),5), filter, filter),
  filter))

Similarly we use VSTACK, to concatenate vertically first information from tasks and then information about projects it self. In your example you have only one project, but it could be more than one.

Finally the description of the projects (Column F)as a lookup of the Project column and drag down the formula:

=IF(E2="","", XLOOKUP(E2,$B$2:$B$7,$C$2:$C$7))

Note: All formulas are places in row 2.