source cell: CCA, BF result cell: C, CC, CCA, B, BF

CCA is a complex code relating to a hierarchy. It means, CCA is child of CC, which is child of C. So, the formula should split into all parents (C and CC) and keep the child (CCA) -- and do this for a concatenated list of strings. The current maximum of levels is four (ABCD) but might be more in the future.

I have no clue how to create a formula for this, but I got this formula: =arrayformula( join( ", ", unique( flatten( transpose( left( split(A2, ", ", true, true), sequence( max( len( split(A2, ", ", true, true) ) ) ) ) ) ) ) ) )

See post

for non-english sheets:

=ARRAYFORMULA(REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(QUERY({IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A1:A3; ", "));;TEXT(ROW(A1:A3); "♥00000"))); "×"))\ 
 SEQUENCE(COLUMNS(SPLIT(A1:A3; ", "))*MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",";;9^9); ", ")))))\ 
 TRIM(FLATTEN(QUERY(IFERROR(REGEXREPLACE(SORT(TRANSPOSE(REGEXREPLACE(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A1:A3; ", "));;REPT(SPLIT(A1:A3; ", ")&"×"; LEN(SPLIT(A1:A3; ", "))))); "×"); 
 {"(×)"\ "("&REPT("."; SEQUENCE(1; MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",";;9^9); ", "))))-1))&")$"}; )&","); 
 SEQUENCE(MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",";;9^9); ", "))))); 0); "^,$"; ));;9^9)))}; 
 "select max(Col3) where Col1 is not null group by Col2 pivot Col1"); "offset 1"; 0);;9^9))); ",$"; ))