Regex remove 2 sections of url at once

user_9599664 0
Viewed 95

I have these possible urls:

https://letterboxd.com/username/film/sometitle/
https://letterboxd.com/username59/film/sometitle/1/
https://letterboxd.com/username59/film/sometitle/2/

I want them all to display like this, without the username and without the trailing number if there is one:

https://letterboxd.com/film/sometitle/

✔ I can remove the trailing number, where it exists, with this:

=IF(REGEXMATCH(A2,"/film/(.*?)/\d/"), left(A2,len(A2)-2), A2)

result: https://letterboxd.com/username59/film/sometitle/ I have to be careful here because if a film's title is just a digit, I don't want it to be replaced, so I specified that there had to be content between /film/ and the trailing digit. (eg, I have to distinguish between film/9/ and film/sometitle/9/

✔ I can remove the username with this (although I'll probably have to also account for other types of characters, come to think of it):

=REGEXREPLACE(A2, regexextract(A2, "\/[A-Za-z0-9]+\/"),"/")

result: https://letterboxd.com/film/sometitle/2/

❌ But I can't for the life of me figure out how to combine both to do it all in one formula! I can perform one of the formulas on the results of the other one, but I can't combine to have it all done in one fell swoop. I've tried too many combinations to list here, but this one I thought was the most promising:

=REGEXREPLACE(A2, REGEXEXTRACT(IF(REGEXMATCH(A2,"/film/(.*?)/\d/"), left(A2,len(A2)-2), A2),"\/[A-Za-z0-9]+\/"),"/")

result: https://letterboxd.com/film/sometitle/2/

It didn't take care of the trailing 2/ and I don't know why. I'm guessing it's to do with some form of circularity? or the order of things?

google-sheets regex
6 Answers
user_46267700

This regex should match what you need. The main assumption is that you don't need anything after third / following the TLD. 

(https?\:\/\/(?:\w*?\.\w+?)*\/(?:.*?\/){3})
  • https?\:\/\/(?:\w*?\.\w+?) This portion is for the url and TLD.
  • *\/(?:.*?\/){3} This captures anything up until the 3rd / and nothing after that.

Instead of replacing, you should be able to just match with the above regex to get the values you want.

user_26729960

An alternative (non-regex) way, may be:

=substitute(join("/", query(split(A2, "/"), "Select Col1, Col2, Col4, Col5"))&"/", ":", ":/")

and see if that works?

user_38894230

You could use 2 capturing groups to capture what you want to keep and use the 2 groups in the replacement.

Then match what you don't want to keep, which will be from the first forward slash and the part that comes after it until the next forward slash and match an optional forward slash and 1 or more digits at the end.

(https?:\/\/[^\/]+)\/[^\/]+(\/film\/[^\/]+)(?:\/\d+)?

Explanation

  • ( Capture group 1
    • https:\/\/[^\/]+ Match http, optional s and ://. Then 1+ times any char except /
  • ) Close group 1
  • \/[^\/]+ Match / and 1+ times any char except /
  • ( Capture group 2
    • \/film\/[^\/]+ Match /film/ and 1+ times any char except a /
  • ) Close group 2
  • (?:\/\d+)? Match optional / and 1+ digits

Regex demo

In the replacement use

$1$2
user_54249880

Here is a regex which works:

https:\/\/([^\/]+)\.com/([^\/]+)/([^\/]+)/([^\/]+)/((\d)+\/)?

From this you get:

Group1 : letterboxd.com

Group3 : film

Group: sometitle

user_116805780

This regex also match what you need:

   C4=REGEXREPLACE(A4,"(\/user[^\/]+|\d+\/$)","")

Demo.

  1. /user[^\/]+ -> match from username... until "/"
  2. \d+\/$ -> match last number at the end of string.

enter image description here

enter image description here

user_126917010
Related