Google Sheet Formula to extract from | separated hiearchy

user_10788105 0
Viewed 164

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.

enter image description here

google-sheets-formula formula google-sheets regex
3 Answers

Try

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

delete everything in A:B range and use:

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

enter image description here

user_56326290

Another possible formula with regular expressions, where the first quantifier {n,n} decides which element to extract from the hierarchy, identifying the number of preceding characters that should not be considered: e.g. L2 = {3,3}. In the example is {6,6} for L3

L2 or L3 is the capturing group (\w+), extracted with $1

=arrayformula(if(C2:C="","",regexreplace(C2:C,"[\w+\|]{6,6}(\w+){1,1}[\w+\|]{3,}","$1")))
user_135594430
Related