Get last position of a character in a string


I need to get sections of an url which looks like this:

I can use the =LEFT() and =RIGHT() functions, but the length of the string can change. How do I get =RIGHT(A1, [las position of /]).

For good measure, can I get the penultimate instance of / too? So I can bring out /Orange-Racoon-15-SIM-Only-(1mth)/546562131

Thanks in advance

This is the function you need: =LEN( )
Additionally I recommend to have a look at: Text Functions - LibreOffice Help
or look into the Help file

Did you mean something like this?


Your can parse a URL using regular expressions. I have done this in combination with the LEN, SEARCH, RIGHT, and LEFT text functions. I am sure this is not the only way to do this (@JohnSUN is offering another method), but I generally rely on pattern matching for these types of tasks. I will break this down so it is clear. Let’s begin with your premise that given a URL:

…what you require is:

  1. /123456789 or
  2. /name_that_I_need/123456789 or
  3. /name_that_I_need

The pattern (1) is the easiest to match as it is at the right-most end of the URL (which we will assume is in B2):


This is taking the right end of the text string for a length of the URL length minus the position at which we find the pattern ([^/][/][^/]*)?$. The pattern is a single non-slash character [^/], followed by a slash [/], followed by zero or more non-slash characters [^/]* and this group is optional (...)? and occurs at line end $. These components form the basis for finding the next two parts of the URL also.

The pattern for (2) is:


This is basically the same function as before but this time with two differences. First, the main search pattern is a single non-slash character [^/], slash [/], zero or more non-slash characters [^/]*, another slash [/], and zero or more non-slash characters [^/]*. Second, the search is being conducted on a trimmed LEFT part of the entire URL. The trimmed bit is using the pattern single slash [/] and zero or more non-slash characters [^/]* i.e., it is searching through everything prior to “/123456789”.

The pattern for (3) is more complex as it essentially modifies (2) to strip the “/123456789” string from the end. It is relatively straightforward if you break it down, it just looks a lot worse than it is.