How to set a Child-Parent system?

Question

I have an Excel file which includes lots of rows of information. I have actually a single problem which is I can't get the parent of each cells according to the information in the cell. It looks like this

In the image, you can see that A has no parent and its' children are A01 and AB and more and more like AC and AD. Is there any way for handling this issue with excel-formulas?

Answer 1

Okey I think I found the answer. Here is my formula

=IF(LEN(B2)=1;"NULL";IF(LEN(B2)=2;LEN(B2;0);IF(LEN(B2)=3;LEFT(B2;1);IF(LEN(B2)=4;LEFT(B2;3);IF(LEN(B2)=5;LEFT(B2;4);IF(LEN(B2)=7;IF(B2;5)))))))

With this formula, I check the length of the characters in cell and get the first part of that string instead of deleting the last indexes because of there are also some string values.

Because of there are some rules with my product codes, I figured out how they are changing and I got the part of the code by their sizes. Thanks for replies, they helped to find this solution.

Answer 2

Assuming that your sample data is true to the format of all your data (there is either 2 numbers at the end of each parent or only an extra letter) then the following formula will work:

Given formula is set to look at data in cell A1, you will have to drag and auto fill the formula down for all rows.

=IF(OR(RIGHT(A1,1)="0",RIGHT(A1,1)="1",RIGHT(A1,1)="2",RIGHT(A1,1)="3",RIGHT(A1,1)="4",RIGHT(A1,1)="5",RIGHT(A1,1)="6",RIGHT(A1,1)="7",RIGHT(A1,1)="8",RIGHT(A1,1)="9")=TRUE,LEFT(A1,LEN(A1)-2),LEFT(A1,LEN(A1)-1))

It works by checking if the last character is a number (with this data excel treats it as text so we have to check for each number as if it is text), if it matches a number then show the parent minus the two right characters otherwise show the parent minus one character.