Search code examples
google-sheetsspreadsheet

Can I find the last instance of a match in a spreadsheet?

I am open to working in LibreOffice or Google Sheets, but I have a long list of values that that are formatted into categories with double colons. So the list kind of looks like:

food::dry good::flour::rye
food::dry good::flour::all purpose
food::dry good::sugar
food::fruit::banana
food::pasta

I want to find the last value in the list. But there might be between 1 and 5 subcategories. I'm trying to figure out a way to maybe count the :: and find the position of the last one, so I can use =RIGHT() to grab everything after that last one.

I want to find a way to pull out just the final name (rye, all purpose, sugar, banana, pasta).

Is this hopeless or is there some kind of "search from the right instead of the left" function that I just don't know exists?

Solution

  • Here's another solution:

    =REGEXEXTRACT(A1,"[^:]*$")

    Or

    =CHOOSECOLS(SPLIT(A1,":"),-1)