Ask Your Question
0

Get last position of a character in a string [closed]

asked 2013-04-25 10:42:13 +0200

Rico gravatar image

updated 2013-06-14 21:40:38 +0200

manj_k gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-30 20:52:49.103105

3 Answers

Sort by » oldest newest most voted
0

answered 2013-04-25 18:39:17 +0200

oweng gravatar image

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:

  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):

=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)))))))

edit flag offensive delete link more
0

answered 2013-04-25 16:35:16 +0200

ROSt52 gravatar image

This is the function you need: =LEN( ) Additionally I recommend to have a look at: https://help.libreoffice.org/Calc/Text_Functions or look into the Help file

edit flag offensive delete link more
0

answered 2013-04-25 18:10:32 +0200

JohnSUN gravatar image

Did you mean something like this?

C:\fakepath\SubstringByCharacter.ods

edit flag offensive delete link more

Question Tools

Stats

Asked: 2013-04-25 10:42:13 +0200

Seen: 5,370 times

Last updated: Apr 25 '13