Can I use an excel formula to extract the link location of a hyperlink in a cell?
You can use a macro:
- Open up a new workbook.
- Get into VBA (Press Alt+F11)
- Insert a new module (Insert > Module)
- Copy and Paste the Excel user defined function below
- Get out of VBA (Press Alt+Q)
Use this syntax for this custom Excel function: =GetURL(cell,[default_value])
Function GetURL(cell As range, Optional default_value As Variant) 'Lists the Hyperlink Address for a Given Cell 'If cell does not contain a hyperlink, return default_value If (cell.range("A1").Hyperlinks.Count <> 1) Then GetURL = default_value Else GetURL = cell.range("A1").Hyperlinks(1).Address End If End Function
I only needed to extract the address from a single cell's value so I found this small function handy:
Instead of a "brute force" macro, you could also create a user-defined function that would extract and return the URL for any hyperlink at which it was pointed:
Function GetURL(rng As Range) As String
On Error Resume Next
GetURL = rng.Hyperlinks(1).Address
End Function
In this case you can place it where you want. If you want, for example, the URL from a hyperlink in A1 to be listed in cell C25, then in cell C25 you would enter the following formula:
=GetURL(A1)
http://excel.tips.net/T003281_Extracting_URLs_from_Hyperlinks.html
function EXTRACT_URL(input) {
var range = SpreadsheetApp.getActiveSheet().getRange(input);
var re = /^.+?\(\"(.+?)\",.+?$/;
if (input.indexOf(':') != -1) {
var formulas = range.getFormulas();
for (var i in formulas) {
for (var j in formulas[i]) {
formulas[i][j] = formulas[i][j].replace(re, "$1");
}
}
return formulas;
} else {
return range.getFormula().replace(re, "$1");
}
}