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.

It's connected to the research data being part of https://raramagnetica.vercel.app/vikus/vismag/


1 Answer 1


Try split() and join(), like this:

=arrayformula( join( ", ", unique( flatten( transpose( left( split(A2, ", ", true, true), sequence( max( len( split(A2, ", ", true, true) ) ) ) ) ) ) ) ) )


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.