extract URL from another cell to be used in importrange function

0
votes

I am trying to optimize some spreadsheets I use at work and I have an idea that would save me a good bit of time. I have a formula which extracts a bunch of data from another spreadsheet and organizes it.

What I would like is to have the spreadsheet hyperlinked in cell A2 and then have a formula in B2 which uses importrange while extracting the url from the hyperlinked text in A2. Is this possible?

1
google-sheetsgoogle-sheets-formula
docs.google.com/spreadsheets/d/… I would like for the formula in B2 to use the url in cell A2 if possible. - Tarik Hodzic

1 Answers

1
votes

if A1 is hyperlink / formula try:

=IMPORTRANGE(REGEXEXTRACT(INDEX(SPLIT(FORMULATEXT(A1), " "),,1), """(.*)"""), 
 "Sheet1!A1")

enter image description here

if A1 is not hyperlinked there were scripts for this but none of them works nowadays

but there is an add-on that can convert non-hyperlinks into hyperlinks:

https://gsuite.google.com/marketplace/app/extract_urls/143780651832

enter image description here

enter image description here