Search code examples
regexgoogle-sheetssplitgoogle-sheets-formulare2

Split string and get last element

Let's say I have a column which has values like:

foo/bar
chunky/bacon/flavor
/baz/quz/qux/bax

I.e. a variable number of strings separated by /.

In another column I want to get the last element from each of these strings, after they have been split on /. So, that column would have: 

bar
flavor
bax

I can't figure this out. I can split on / and get an array, and I can see the function INDEX to get a specific numbered indexed element from the array, but can't find a way to say "the last element" in this function.

Solution

  • Edit: this one is simplier:

    =REGEXEXTRACT(A1,"[^/]+$")

    You could use this formula:

    =REGEXEXTRACT(A1,"(?:.*/)(.*)$")

    And also possible to use it as ArrayFormula:

    =ARRAYFORMULA(REGEXEXTRACT(A1:A3,"(?:.*/)(.*)$"))

    Here's some more info:

    This formula will do the same:

    =INDEX(SPLIT(A1,"/"),LEN(A1)-len(SUBSTITUTE(A1,"/","")))

    But it takes A1 three times, which is not prefferable.