Regex to find URL without the file name and extension-CodePudding

I have a Google sheet with hundreds of images URLs from Dropbox and I want to write a regex that will find all the prefixes part of the URLs without the file name extension. I'll then replace the search result with another url.

What I have:

https://www.dropbox.com/s/dtauvpuy3a5qyu4/A1001.jpg
https://www.dropbox.com/s/dtauvpuy3a5qyu4/A1001.jpg
https://www.dropbox.com/s/dtauvpuy3a5qyu4/A1001.jpg
https://www.dropbox.com/s/d0xedx0j72v5uub/A1002-1.jpg
https://www.dropbox.com/s/d0xedx0j72v5uub/A1002-1.jpg

What I expect:

https://anotherurl.com/A1001.jpg
https://anotherurl.com/A1001.jpg
https://anotherurl.com/A1001.jpg
https://anotherurl.com/A1002-1.jpg
https://anotherurl.com/A1002-1.jpg

Please, how can I do that?

CodePudding user response：

Suppose your original URLs are in the range A2:A of some sheet. Clear some other column entirely (e.g., B:B), and place the following formula in B2:

=ArrayFormula(IF(A2:A="",,REGEXREPLACE(A2:A,"(. //). (/[^/] )$","$1"&"another.url"&"$2")))

Where you see "another.url", you can either manually enter some other URL text between the quotation marks, or you can enter the new URL text is some cell (say, B1) and use the cell reference instead:

=ArrayFormula(IF(A2:A="",,REGEXREPLACE(A2:A,"(. //). (/[^/] )$","$1"&B1&"$2")))