How to Extract Actual Addresses from Hyperlinks in Excel

Excel is a powerful tool. You can use VBA code in Excel to extract actual addresses from hyperlinks. By looping through the range of cells containing hyperlinks, the code checks if each cell has a hyperlink, extracts the address, removes any prefixes like "mailto:" or "tel:", and updates the adjacent cell with the extracted address. With this VBA solution, you can automate the process of extracting actual addresses from hyperlinks in Excel, saving time and effort.

Here are the steps of how you can do it:

Step 1

Open the Excel file in which you want to export excel data to text files.

Press Alt + F11 to open the Visual Basic Editor in Excel.

Insert a new module by clicking on "Insert" and selecting "Module."

In the module window, paste the following code:

Example

Sub ExtractHyperlinkAddresses()
    Dim rng As Range
    Dim cell As Range
    Dim address As String
    
    'Specify the range containing the hyperlinks
    Set rng = Range("A2:A6") 'Update with your desired range
    
    'Loop through each cell in the range
    For Each cell In rng
        'Check if the cell contains a hyperlink
        If cell.Hyperlinks.Count > 0 Then
            'Extract the address from the hyperlink
            address = cell.Hyperlinks(1).Address
            
            'Remove any "mailto:" or "tel:" prefixes
            address = Replace(address, "mailto:", "")
            address = Replace(address, "tel:", "")
            
            'Update the cell value with the extracted address
            cell.Offset(0, 1).Value = address
        End If
    Next cell
End Sub

Step 2

Modify the line Set rng = Range("A2:A6") with the range that contains your hyperlinks. This specifies the range where you want to extract the addresses.

Save the module and close the VBA editor.

Step 3

Press Alt + F8 to open the macro dialog box.

Select the "ExportToTextFile" macro from the list and click "Run".

Step 4

In current sheet you get the addresses from the hyperlinks as follows:

Conclusion

To extract actual addresses from hyperlinks in Excel using VBA, you can use the provided code. It loops through a specified range, checks for hyperlinks, extracts the address, removes any prefixes like "mailto:" or "tel:", and updates adjacent cells. Adjust the range as needed. This VBA solution automates the process, making it efficient for handling large datasets. Remember to open the VBA editor, insert the code into a new module, update the range, and run the macro. The extracted addresses will be populated in the adjacent cells, allowing you to work with the actual addresses rather than hyperlinks.

Pradeep Kumar

e

Updated on: 20-Jul-2023

295 Views

Kickstart Your Career

Get certified by completing the course

Get Started