CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Tarik Hodzic
Tarik Hodzic

Reputation: 357

extract URL from another cell to be used in importrange function

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?

Upvotes: 0

Views: 1096

Answers (1)

player0
player0

Reputation: 1

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

Upvotes: 1

Related Questions