Regex pattern help

[neilr]

Hi I want to extract a portion of a text from a google drive share link.

The format of the drive link is like this:
https://drive.google.com/file/d/1IklI1OAW8XLtTKDfwW75BNcve8z8xQ57/view 

I want to extract the text after "d/" and will end at before "/view". So the intended result should be "1IklI1OAW8XLtTKDfwW75BNcve8z8xQ57 "

Any ideas how?

Thanks a lot!

[alexandrea]

I think you can use the SUBSTITUTE function to achieve this. I did some testing with this formula I wrote and hopefully it helps!

SUBSTITUTE(SUBSTITUTE({link},"https://drive.google.com/file/d/",""),"/view","")

Replace {link} with your correct field name, and it will pull the text you're looking for. 

How it works:

First, the formula calculates the inner-most part of the formula which is:

SUBSTITUTE({link}, "https://drive.google.com/file/d/", "")

This searches your field for the string "https://drive.google.com/file/d/" and replaces it with an empty string. 

Then the formula evalutes this new string against the outermost formula

SUBSTITUTE("INNER-FORMULA"), "/view", "") 

It searches the new string for "/view" and replaces it with an empty string, leaving you with only the string you are looking for! 

[Sho]

Hi @neilr ,

In the case of REGEX, it is like this.

REGEX_EXTRACT({URL},"/d/(.*)/view")