Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Power Query extract URL???

0

I can't find the answer anywhere. This applies to more than 1,000 webpages all with different structures.

QUESTION ..... When I import webpage html tables with Power Query, how do I get it to extract the URLs that are embedded in each person's name????

Answer
Discuss

Discussion

Did you see the Answer I provided (or Willie's)? If so, why no response?
John_Ru (rep: 6212) Sep 1, '23 at 3:04 am
Add to Discussion

Answers

0

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.

Discuss

Discussion

@Willie - good try but you couldn't use a UDF to extract a URL from the results of a Power Query since ordinarily they'd produce text (without the underlying links on the webpage). I've just posted an alternative answer/ file using PQE but the lack of any response to your post doesn't fill me with hope of receiving any reply (although this is a user with a reputation from the past- before my time though).
John_Ru (rep: 6212) Aug 24, '23 at 10:54 am
@Willie - further to my note above, are you getting email alerts from the Forum (e.g. to me posting this against your Answer)? I don't get them anyway (I've had that problem since August 2022 so have to check the site) but once again the proportion of responses seems to be very low.
John_Ru (rep: 6212) Aug 25, '23 at 3:16 am
@John
Since I don't have experience with Power Query I was unaware of how it returns results (as text). Once again, you have taught me something new.
As for email alerts, I too have stopped getting them and only know of comments after logging in. I agree that lately fewer posters are answering questions for more details, or to even say thanks for providing an aswer. Discouraging but I will continue to provide help where and when I can.
WillieD24 (rep: 557) Aug 26, '23 at 5:41 pm
@Willie - thanks for replying. Not getting emails alerts is a problem for everyone (and has happened before, after the site hosts have updated the site). Normally I'd let Don know but he's away for a couple of weeks. Thanks for continuing to help on the Forum- we can all learn from solving the problems of others and from the experiences of others. 
John_Ru (rep: 6212) Aug 26, '23 at 5:49 pm
@Willie - I've emailed Don about the alerts but guess it will be a week or more until he's back. 
John_Ru (rep: 6212) Aug 27, '23 at 3:29 am
Add to Discussion
0

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.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login