Microsoft Excel is words most widely used data analytics tool. Its most widely used even before widespread usage of term data analytics.
Fetching hyperlink from Hyperlinked cell
Many times we have to deal with hyperlinks in excel. If you have a text from which you want to extract hyperlink, there is no readymade formula available. However, you can do it multiple ways, simplest way is to create your own custom fucntion
- Press Alt+F11
- Add following code
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
- type =GetURL in any cell and select cell having hyperlink, it will fetch only hyperlink.
How to Add hyperlink to a sell
If you have hyperlink column in your excel and you want to add hyperlink to any text, this can be done using following formula