I have copied from a website a series of hyperlinks and pasted them in a google sheet. The values show up as linked text, not hyperlink formulas, and are still linked correctly. For each row, I'm trying to extract the URL ONLY (not the friendly text) and insert it into the adjacent column. How could this be accomplished using a formula?
For example:
=SOMEFUNCTION(cellThatHoldsLink, returnedURLOnly)
This and similar scenarios do not apply because the pasted data are not formulas. I will accept a script (GAS) solution, or any solution for that matter, but would prefer if it could be done using a formula. I have found dozens of HYPERLINK manipulation scripts, but nothing on this particular scenario, or even how to access the property that is holding that url. Thanks.
You can use importxml to pull in the whole data table and it's contents with this:
=IMPORTXML(A1,"//tr")
next to it we pulled in the url strings in the tags
=IMPORTXML(A1,"//tr/td[1]/a/@href")
and finally concatenated the strings with the original domain to create your hyperlinks
=ARRAYFORMULA("http://www.bnilouisiana.com/"&INDIRECT("A2:A"&COUNTA(A2:A)))