Hi
I need to get sections of an url which looks like this:
http://www.thewebsiteineed.com/cell/Orange-SIM/Orange/Orange-Racoon-15-SIM-Only-(1mth)/546562131
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
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:
http://www.domain.com/path/to/the/part/of/the/name_that_I_need/123456789
…what you require is:
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):
=RIGHT(B2,LEN(B2)-SEARCH("([^/][/][^/]*)?$",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:
=RIGHT(B2,LEN(B2)-SEARCH("([^/][/][^/]*[/][^/]*)?$",LEFT(B2,(SEARCH("([/][^/]*)?$",B2)))))
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.
=LEFT(RIGHT(B2,LEN(B2)-SEARCH("([^/][/][^/]*[/][^/]*)?$",LEFT(B2,(SEARCH("([/][^/]*)?$",B2))))),SEARCH("([^/][/][^/]*)?$",RIGHT(B2,LEN(B2)-SEARCH("([^/][/][^/]*[/][^/]*)?$",LEFT(B2,(SEARCH("([/][^/]*)?$",B2)))))))