Power Query extract URL???

Hi gzfraud and welcome.

I use 2007 and 2016 so there are things 365 has which I don't use.

From your description I take it the names have hyperlinks to a website.

Excel does not have a built-in function to extract a URL, but it is possible by creating a UDF (user defined function) using VBA such as this one:

Function GetURL(rng As Range) As String
    On Error Resume Next
    GetURL = rng.Hyperlinks(1).Address
End Function


' Now in your worksheet, enter function =GetURL(A1) and hit enter.
' OR
' create a looping macro

If you need help writing a macro please upload a sample file.

If this helps, please mark this answer as selected.

HI Gzfraud

In the attached file, I've modfied/ extended the Power Query M code I found on another webpage (Chris Webb's Using Text.BetweenDelimiters() To Extract URLs From A Web Page In Power BI/Power Query M) to create the query GetURLs which will:

1. accept a SourceURL (e.g https://www.teachexcel.com/talk/7386/power-query-extract-url for this webpage)
2. call a subfunction SubURLextract34  which:
   • finds any links on that page starting with http//: or  https//: using the double quote separator " (ASCII character 34 decimal) 
3. call a similar subfunction SubURLextract39 which does the same but for the (also permissible) single quote separator ' (ASCII character 39 decimal)
4. combine the results from those into a single list of URLS.

The Power Query M code for the GetURLs function is just:

(SourceURL as text) =>
let
    Output= SubURLextract34(SourceURL) & SubURLextract39(SourceURL)

in

    Output

You can see the code for this (and the more complex subs) in the Advanced Editor in the Power Query dialogue. Warning- when you open the attached file, you'll get a notice about external connections- this relates to the webpage used in the query.so you're safe to enable.

To test another webpage from the attached file, open Power Query, double  click on GetURLs under Queries then paste your URL under Enter Parameter/ SourceURL and click Invoke. You may have to provide credentials (dependent on the webpage) but should get a list of URLs (e.g. in the attached file, this webpage produced 29 links).

Hope this helps. If so, please remember to mark this Answer as Selected.