Change parent/child to hierarchical address

My GL system defines the Chart of Accounts in a parent/child form. It allows for nine levels of depth. An excerpt is in the table below.

COA	DEPTH	ID	PARENT ID
NETINCOME	1	1
NETINCB4TAX	2	2	1
OPERINC	3	3	2
TOTOPERREV	4	4	3
TOTNETREV	5	5	4
NETPATREV	6	6	5
GRSPATREV	7	7	6
INPAT	8	8	7
IPREVBASE	9	9	8
IPCAPITATED	9	10	8
IPNONCON	9	11	8
IPFFS	9	12	8
IPOTHER	9	13	8
IPUNINSURED	9	14	8
OUTPAT	8	15	7
OPREVBASE	9	16	15
OPCAPITATED	9	17	15
OPNONCON	9	18	15

The system has several miscellaneous fields which I can populate with string (up to 30 characters) values.

My idea is to flatten the parent/child relationship into a hierarchical address.

For instance, NETINCOME is the top of my chart and I've assigned it an address value of "001000000000000000000000000". The address is comprised of nine 3-digit values (LEVELS) representing NETINCOME's place in the hierarchy:

• Level 1 = 001
• Level 2 = 000
• Level 3 = 000
• Level 4 = 000
• Level 5 = 000
• Level 6 = 000
• Level 7 = 000
• Level 8 = 000
• Level 9 = 000

GRSPATREV is a descendant of NETINCOME and is several levels of depth down. I've assigned it an address of "001001001001001001001000000".

• Level 1 = 001
• Level 2 = 001
• Level 3 = 001
• Level 4 = 001
• Level 5 = 001
• Level 6 = 001
• Level 7 = 001
• Level 8 = 000
• Level 9 = 000

INPAT is a direct descendant of GRSPATREV. I've assigned it an address of "001001001001001001001001000"

• Level 1 = 001
• Level 2 = 001
• Level 3 = 001
• Level 4 = 001
• Level 5 = 001
• Level 6 = 001
• Level 7 = 001
• Level 8 = 001
• Level 9 = 000

I've done this manually and am looking for a formula that would do this for me and hoping someone can help.

I'm attaching a workbook with the results of my manual input. The red cells indicate differences between the cells above and are not necessary to the formula.

There are occasional changes to our COA and I don't want my future successors to throw their hands up in the air and curse my parentage when they inevitably need to make a change.

If I can provide any other information, please let me know.

Thanks.