Hey there, let's tackle your two main issues step by step—getting the actual company website URL from those Google search links you created, and extracting URLs from Excel cells when you need them.

Your current HYPERLINK links point to Google's search results page, not directly to the company's website. To automatically pull the target site URL from those results, here are two reliable methods:

Option 1: Power Query (No Macros Required) #

This is a built-in Excel tool that lets you scrape web data without coding:

• First, turn your column of search links into an Excel table (select the column > press Ctrl+T, check "My table has headers" and name the column something like SearchLinks).
• Go to the Data tab > click From Table/Range to open the Power Query Editor.
• Click Add Column > Custom Column, then paste this formula into the editor:

  = let
      WebPage = Web.Page(Web.Contents([SearchLinks], [Headers=[#"User-Agent"="Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36"]])),
      ResultsTable = WebPage{0}[Data],
      FirstResultURL = ResultsTable{0}[Link]
    in
      FirstResultURL
  

  This loads the search results page, grabs the first result's link (usually the company's official site), and returns it.
• Click Close & Load to bring the extracted URLs back into your Excel sheet.

Note: If you hit privacy prompts, go to File > Options and Settings > Query Options > Privacy and select "Ignore privacy level settings". For large batches (hundreds of links), process them in chunks to avoid Google's rate limits.

Option 2: VBA (More Customizable) #

If you prefer using macros, this function will scrape the first search result's URL:

Function GetCompanyWebsite(searchURL As String) As String
    Dim ie As Object
    Dim doc As Object
    Dim resultLink As Object
    
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = False ' Set to True if you want to see the browser window
    
    ie.Navigate searchURL
    ' Wait for the page to fully load
    Do While ie.Busy Or ie.ReadyState <> 4
        DoEvents
    Loop
    
    Set doc = ie.Document
    ' Grab the first search result link (adjust selector if Google updates its UI)
    On Error Resume Next
    Set resultLink = doc.querySelector("div.g a")
    If Not resultLink Is Nothing Then
        GetCompanyWebsite = resultLink.href
    Else
        GetCompanyWebsite = "No website found"
    End If
    On Error GoTo 0
    
    ie.Quit
    Set ie = Nothing
    Set doc = Nothing
End Function

To use it, enter =GetCompanyWebsite(A1) in a cell (replace A1 with your search link cell) and drag it down for all entries. Remember to enable macros when opening the file.

Depending on how your URLs are stored, use one of these methods:

For URLs in the HYPERLINK Function #

If your cell uses =HYPERLINK("url", "company name"), use this formula to pull the URL out:

=MID(FORMULATEXT(A1), FIND("""", FORMULATEXT(A1)) + 1, FIND("""", FORMULATEXT(A1), FIND("""", FORMULATEXT(A1)) + 1) - FIND("""", FORMULATEXT(A1)) - 1)

This grabs the text between the first two quotes in the cell's formula, which is your URL.

For Manual Hyperlinks (Not Using HYPERLINK) #

If you added hyperlinks manually (right-click > Hyperlink), use this VBA function:

Function ExtractHyperlinkURL(cell As Range) As String
    If cell.Hyperlinks.Count > 0 Then
        ExtractHyperlinkURL = cell.Hyperlinks(1).Address
    Else
        ExtractHyperlinkURL = "No hyperlink found"
    End If
End Function

Use it by entering =ExtractHyperlinkURL(A1) in a cell to get the hyperlink's address.

内容的提问来源于stack exchange，提问作者Peter