CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Digital Farmer
Digital Farmer

Reputation: 2107

Extract cell value to only between the third '/' and the fourth '/'

Values in column A

/teams/brazil/esporte-clube-vitoria/306/
/teams/brazil/gremio-esportivo-brasil/6205/

Expected values:

esporte-clube-vitoria
gremio-esportivo-brasil

Formula that I currently use but that generates errors when used with ARRAYFORMULA for various values:

=ARRAYFORMULA(IFERROR(TRIM(MID(SUBSTITUTE(A1:A,"/",REPT(" ",99)),299,99))))

Is there a more reliable formula for this job?

Upvotes: 0

Views: 54

Answers (1)

marikamitsos
marikamitsos

Reputation: 10573

You can try the following formula

=ArrayFormula(REGEXREPLACE(R1:R,"/\w+/\w+/|[0-9]|/",""))

enter image description here

Formula that I currently use but that generates errors when used with ARRAYFORMULA for various values

Cannot test if this formula works for other values unless you share them.

One could also use

=ArrayFormula(INDEX(SPLIT(R2:R,"/"),,3))

Upvotes: 3

Related Questions