TL;DR – quick generation of a Regex string inside a google sheet formula. I have leveraged other online Regex tools, but this makes it seamless to implement as a working formula. Allowed me to stay focused on the overall task and not dive into the vagaries of Regex and Array formulas.
I need a google sheet formula in Column G to extract the docID from this URL in Column R: https://docs.google.com/document/d/VCN6rGL1Ftrysyg9J8cyk/edit
The formula you can use in Column G to extract the docID from the URL in Column R is:
| =REGEXEXTRACT(R2,”/d/([a-zA-Z0-9-_]+)”) |
This uses the REGEXEXTRACT function to extract the docID from the URL, which is the string of characters between “/d/” and the next “/” in the URL. The docID will be captured in the parentheses in the regular expression, which is passed as the first argument to the REGEXEXTRACT function. The second argument is the cell containing the URL that you want to extract the docID from.
Note: This formula will extract the docID from the given URL format only. If the URL format is different this formula will not work.
Can you make this an array formula?
Yes, you can use an array formula to extract the docID from multiple URLs in a range of cells. To do this, you can use the following formula:
| =ARRAYFORMULA(REGEXEXTRACT(R2:R, “/d/([a-zA-Z0-9-_]+)”)) |
This uses the ARRAYFORMULA function to apply the REGEXEXTRACT function to a range of cells (R2:R) rather than just a single cell. The range of cells containing the URLs is passed as the first argument to the REGEXEXTRACT function. The second argument is the same regular expression used in the previous formula.