Google Sheet Formula to extract from | separated hiearchy-CodePudding

I have a hierarchy list separated by |. The hierarchy is listed as L1|L2|L3|L4|L5 ect.

I'm trying to create a formula to extract the L2 and L3 of the hierarchy. The best method I've found for now is using the Text to Columns feature in Google Sheets but a formula would be ideal. Giving some examples below.

CodePudding user response：

Try

=query(arrayformula(if(C2:C="",,split(C2:C,"|"))),"select Col2,Col3")

CodePudding user response：

use:

=INDEX(ARRAY_CONSTRAIN(IFNA(SPLIT(REPT(REGEXEXTRACT(C2:C, 
 "\|(. )")&"|", 2), "|")), 9^9, 2))