I have a list of several values. I need to understand which of them is parent and which is child. How to do it in Excel?

I have a list in Excel like this:

You can see that A01->A0101->A010101, it’s a chain where we have parents and children.

I need to create a CSV file, where I can see their parent ID and child ID. Like here:

The question: How to make it possible in Excel? I have idea how to do it with C#, but I need to do it in Excel.

Which result I would like to see:

1. A01 – ID should be 1
2. A0101 – ID should be 2 | Parent ID – 1
3. A010101 – ID should be 3 | Parent ID – 2

In the columns it will be like this:

1. cell – 1
2. cell – 2 | cell – 1
3. cell – 3 | cell – 2

So, I would like to have list only with 2 columns, where I can see ID of the A01 and the parent ID.

In the link below you can see how it should work if you want to put a CSV file in JIRA.

Link:
https://confluence.atlassian.com/jirakb/how-to-import-issues-with-hierarchy-from-a-csv-into-jira-software-1087512458.html

>Solution :

Here is one option using Excel ms365 functions:

Formula in B2:

=LET(x,A2:A18,y,SEQUENCE(ROWS(x)),HSTACK(y,XLOOKUP(LEFT(x,LEN(x)-2),x,y,"")))