Split string and get last element

regex google-sheets split google-sheets-formula re2
Max Williams picture Max Williams · May 23, 2016 · Viewed 21.6k times · Source

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.

Answer

Max Makhrov picture Max Makhrov · May 23, 2016

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.

Related questions

How do I combine COUNTIF with OR

In Google Spreadsheets, I need to use the COUNTIF function on a range with multiple criteria. So in the table below, I would need to have something like =COUNTIF(B:B,"Mammal"or"Bird") and return a value of 4. A |…

Read More
find & replace commas with newline on Google Spreadsheet

I've been trying to figure out how to replace commas with line-breaks. I've tried using (/r/n/), char(10), the Unicode representation (can't remember now), but it rather predictably replaces the comma with whatever I type in the other box, not …

Read More
Extract digits from string - Google spreadsheet

In Google spreadsheets, I need a formula to extract all digits (0 to 9) contained into an arbitrary string, that might contain any possible character and put them into a single cell. Examples (Input -> Output) d32Ελληνικάfe9j.r/3-fF66 …

Read More