Need Help For Regular Expression For Google Drive Folder URL

user_14566169 0
Viewed 230

I'm trying to extract the Google Drive Folder ID on Google sheets formulas. The issues is there are two type of URL are pasted by different Individuals in the sheet.

With the sharing ? mark at the end
https://drive.google.com/drive/folders/1GY5VHUvkzjrq7vKbrFJG_9ALnF_TEeoB?usp=sharing

Without the sharing ? mark at the end
https://drive.google.com/drive/folders/1GY5VHUvkzjrq7vKbrFJG_9ALnF_TEeoB

I used this =REGEXEXTRACT(I2, ".*/(.*)") which doesn't work for above both links, how can i modify the formula that will extract only the ID of above two tyes of URL?

Thanks in Advance

google-sheets-formula google-sheets regex
1 Answers

You can use

=REGEXEXTRACT(I2, ".*/([^?]+)")

See the regex demo. Details:

  • .* - zero or more chars other than line break chars as many as possible
  • / - a / char
  • ([^?]+) - Group 1 (the actual result returned by REGEXEXTRACT will be this captured text): one or more chars other than a ? char.

See a Google sheets test:

enter image description here

user_38329700
Related