Search code examples
regexif-statementgoogle-sheetslambdare2

Google Sheets - remove everything between 2 specific characters?

I need to shorten a lot of links to Getty images, here are some examples:

Here's how I want them to appear:

So basically I need to remove this from each link (I can add in the "license" afterwards just using the find/replace function):

  • detail/news-photo/an-aerial-photograph-of-the-many-sprawling-buildings-at-sea-news-photo
  • detail/news-photo/artificial-sunlight-is-being-used-as-a-treatment-for-bone-news-photo

How can I do this? Thanks!

I found this question which is similar to mine but the answer was confusing since I'm not sure where I put (?i), .*?[CM]?L

Solution

  • try:

    =INDEX(IFNA("https://www.gettyimages.com/license/"&
 REGEXEXTRACT(A1:A, "/(\d+)/?$")))

    enter image description here

    \d           number
\d+          numbers
(\d+)        group of numbers
/(\d+)       group of numbers after slash
/(\d+)$      group of numbers after slash from end
/(\d+)/?$    group of numbers between two slashes if url ends with slash 
             if not then get group of numbers after slash from end