Today, I faced the problem of extracting URLs from a long list of hyperlinked text. I had two options:
First is to do it manually:
- Right-click a hyperlink.
- From the Context menu, choose Edit Hyperlink. Excel displays the Edit Hyperlink dialog box.
- Select and copy (Ctrl+C) the entire URL from the Address field of the dialog box.
- Press Esc to close the Edit Hyperlink dialog box.
- Paste the URL into any cell desired.
But then I have a long list of hyperlinks in my Excel worksheet. If I had to do this for each and every single hyperlink, this can get tedious very very quickly. So the second option is to get the URLs using a macro.
The following example can be useful when extracting hyperlinks from tables of links that have been copied into Excel, when doing post-processing on Excel web queries, or getting the email address from a list of “mailto:” hyperlinks.
Extracting a URL from a hyperlink on Excel is easy!
- Open up a new workbook.
- Get into VBA (Press Alt+F11)
- Insert a new module (Insert > Module)
- Copy and Paste the Excel user defined function below
- Get out of VBA (Press Alt+Q)
- Use this syntax for this custom Excel function: =GetURL(cell,[default_value])
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
48 Comments
Thank you!!! You just saved me about 30 mins of work!
You are welcome! I am happy that my post saved you a lot of time!
Thanks a lot!! Could save my time.
Good stuff. Big thanks to Karen.
Ummmm, these are horrible instructions for a total newb like me. How do I apply this to all of my cells with anchor-texted-hyperlinks in them?
You are a life saver! that’s 4000+ lines worth of work..
thanks!
If you were in the same room as me right now I would hug you. Life saver!
So happy I could help
Hi Karen,
Seems like this could save me a ton of time, however I keep getting an error message.
Operating in Excel 2007
I followed your directions
When I hit enter I go immediately back to MVB
Pop Up Compile error: Invalid outside procedure
Book 2 – Sheet 2 (Code)
Set rng = rng(1)
If rng.Hyperlinks.Count = 0 Then
GetURL = “B”
Else
GetURL = rng.Hyperlinks(1).Address
End If
End Function
Behind that in another box is
Book 2 – Module1 (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
I do not have super strong excel skills but follow directions well. Please help me if you can. Thank you in advance, I am certain I am doing something small wrong
-Jeff
Jeff, I’m sorry to hear that you are getting these errors. You mentioned that you are seeing the error when you hit “enter”. Can you clarify on which step you are hitting “enter”? I don’t think there’s a step that requires you to do that. I Did you try these steps below? Can you try these again?
1. Open up a new workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste the Excel user defined function above.
5. Get out of VBA (Press Alt+Q)
6. Use this syntax for this custom Excel function: =GetURL(cell,[default_value])
Morning, I thought the whole day going to the 4700 lines, but now half a minute to complete. Thank you very much!
Glad to hear this saved you time!
Thank you for posting this! However I followed your steps. I am familiar with excel functions.
Your code assumes the email “mailto” url is in cell A1, correct? This is where I have hyperling “john smith” which is “mailto@johnsmit@mail.com”
I put the =GetURL(cell,[default_value]) into cel B1 but nothing happens, or I get an error.
I am sure I am close!
Steve; excel 2010.
Hi Steve! You are indeed very close! Just put =GetURL(A1) and that should work. If you have an entire column of URLs, you can just drag that formula all the way down. Let me know in case it doesn’t work. Good luck!
I’m trying to extract email addresses from hyperlinked picture objects (email symbols) which I have placed in an Excel file. I followed your steps. But it is not doing the job. Any suggestions? Best regards, Donnell
I am sorry to hear that they didn’t work? Did you try these steps below? It should be pretty simple.
1. Open up a new workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste the Excel user defined function above.
5. Get out of VBA (Press Alt+Q)
6. Use this syntax for this custom Excel function: =GetURL(cell,[default_value])
Hi Donnell! I have not tried this method for hyperlinked pictures, but I suspect that it won’t work because pictures are not really within cells. The only thing I can think of is doing it manually, but that’s really tedious. If there’s no way to do it except manually, consider hiring someone on Odesk.com to do it for you for a few bucks instead
I am sorry that I couldn’t be of more help!
Thank you VERY much!
You’re welcome, Chevy! Glad I was able to help
it’s worked, it worked!!! thanks!!!!!
Happy to hear that it worked for you!
Thanks!!!
You’re welcome, Jake!
Wow it worked
even in 2013 version file…Thank you very much 
So happy it worked for you, Lowrance!
Okay, I understand the macro part but what is step 6 referring to with “Use this syntax for this custom Excel function: =GetURL(cell,[default_value])”
What is a syntax, where do I copy and paste “=GetURL(cell,[default_value])”
If helps, the hyperlinks I’m trying to convert are in cells ranging from G18 to G50.
Making use of =hyperlink formula with text and multiple cells. At first this did not work, but copy and paste into word then back to excel and now I got all the full URLs!
Thank you for your suggestion! I have not tried that function as a way to extract URLs, but I’m happy to hear that it worked for you
Brian, you can just use the function “=GetURL(cell,[default_value])” on your Excel sheet as you would any other standard Excel formula. For example, if cell A1 contains the hyperlinked text whose URL who you want to extract, you can just type “=GetURL(A1)” on any other cell on the sheet to extract the hyperlink. Let me know if I can answer any other questions! I’ll be happy to help.
How can I save it permanently in excel? Because everytime I open the excel this function is gone
I think where some people are getting confused is with the [default_value] portion. Here is an example of what will work. Data is in Row 1 Column A. Click on Row 1, Column B. Use =GetURL(A1,) . This should work.
This ROCKS!! Saved me some tedious work! THANK YOU!!
Paul, it makes me so happy to hear that I’ve saved you time
Glad to hear this post helped!
I’m getting everything to work Karen but for some reason the cell says mailto:(person’semailaddress) but when I click it to try and copy and past it just says the formula again. What am i doing wrong? I just want it to read as the person’s email address it would save me hours of time.
Oh that’s easy to do, Taylor. Just follow these steps:
1. Highlight the entire column containing the formulas.
2. Press CTRL-C on your keyboard to copy the content of all the cells in that column.
3. Right-click and select “Paste Special…”
4. Select “Values”.
6. Click the “OK” button.
7. Press CTRL-F to launch the “Find” popup.
8. Enter “mailto:” next to “Find what:”.
9. Click the “Replace” tab.
10. Click the “Replace All” button.
Hope this helps!
awesome..
Glad you found it helpful, Khan
Great Code… Worked Well, and saved huge time….
Cheers..
I was trying in office 2010 but not working to get the link.. please help
Jagan, sorry to hear that it was not working for you. I believe it does work for Office 2010 too. Could it be that your macros are just disabled? At which step is it failing?
Glad it helped, Rohit!
Jason, if you are using the same Excel file, it should remain there. However, if you are creating a new file, you have to re-insert the macro.
Fantastic! now imagine if we could sum up all the hours that you saved from everyone I’d guess a few thousands hrs.
Thanks a bunch
OG
Oscar, I’m so happy to hear that it saved you a lot of time
Thank you, your post saved almost half a day for me
So happy that my post helped you save so much time
Thanks for this
You’re very welcome, Brian