Split string and get last element

Question

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 1

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:

• the RegExExtract function
• Some good examples of syntax
• my personal list of Regex Tricks

---

This formula will do the same:

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

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

Solution 2

Also possible, perhaps best on a copy, with Find:

.+/ 

(Replace with blank) and Search using regular expressions ticked.

Comments

[Connor]

Guessing the downvotes are because this is in Java and the question is about using google sheets

[Jeremy Caney]

What are the benefits or tradeoffs of this approach compared to the options outlined in the accepted answer?