50

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Max Williams
  • 32,435
  • 31
  • 130
  • 197

4 Answers4

78

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.

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
4

You could do this too

=index(SPLIT(A1, "/"), COLUMNS(SPLIT(A1, "/"))-1)
Cedric
  • 41
  • 3
  • 1
    What are the benefits or tradeoffs of this approach compared to the options outlined in the accepted answer? – Jeremy Caney Jun 23 '21 at 19:57
  • The advantage is that it does not require an understanding of regex. I like regex, but i didn't always – KCD Nov 30 '22 at 23:37
2

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

.+/

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

pnuts
  • 58,317
  • 11
  • 87
  • 139
-6

You can try use this!

You've got the array of String, so you can acess the last element by length

String message =  "chunky/bacon/flavor";
String[] outSplited = message.split("/");

System.out.println(outSplited[outSplited.length -1]);
Tomerikoo
  • 18,379
  • 16
  • 47
  • 61
Armando Rafael Costa
  • 1
  • 6
    Guessing the downvotes are because this is in Java and the question is about using google sheets – Connor Sep 30 '19 at 22:36