4

I want to get last occurrence from multiple occurrences of a character in LibreOffice Calc.

For e.g. I have a string abc1ba2ac2adeaa43add. Now if I am searching for a it should return 18.

HexAndBugs
  • 5,549
  • 2
  • 27
  • 36
Ashvin Kanani
  • 831
  • 2
  • 14
  • 22

2 Answers2

3

Use regex:

=SEARCH("a[^a]*$"; CELL)

To search for another character than a, simply replace both a's with the character you want to search for.

Alexander Christiansson
  • 555
  • 3
  • 7
  • That's a link to OpenOffice. LibreOffice doesn't use regex patterns in its SEARCH function. – Stevens Miller Oct 31 '20 at 18:33
  • Just in case anyone finds this - LibreOffice does use REGEX in formulas, but only after you enable it in Options under LibreOffice Calc -> Calculate -> Formulae Wildcards – Shajirr Apr 12 '22 at 16:16
2

here's one that i cooked up recently - it answers the question without using regex (which for some reason, i couldn't get to work with libreoffice 4.3.1.2)

=FIND("+|",SUBSTITUTE(A1,"a","+|",(LEN(A1)-LEN(SUBSTITUTE(A1,"a","")))))

basically, it does the following:

  1. replace the last instance of a particular search string with a marker

    SUBSTITUTE( A1, "a", "+|", (LEN(A1)-LEN(SUBSTITUTE(A1,"a","")))) )

  2. find the index of the marker

    FIND( "+|", ... )

- or to return the string to the right of 'a' -

=MID(A1, FIND("+|",SUBSTITUTE(A1,"a","+|", (LEN(A1)-LEN(SUBSTITUTE(A1,"a","")))))+1, LEN(A1))

in this example, "a" is the last instance of a string you're looking for and "+|" is a placeholder that musn't already exist in the string you are searching

everything trailing the spec'd string is returned by the call to mid

brynk
  • 606
  • 3
  • 8
  • Thanks - this was perfect for extracting the file extension from a file name. I just changed the references to "a" to "." – Rod Apr 20 '18 at 10:29