By far, the best tutorial on this function. May I please add that for MAC users, the Developer tab may not be visible for them to open VB. And I honestly don't know what F11 is since when I hit Alt F11, it opened sound. So I found this advice to manually open VB on a Mac: The Developer tab is the toolbar that has the buttons to open the VBA editor and create Form Controls like buttons, checkboxes, etc. To display the Developer tab, click on Preferences under the Excel menu at the top of the screen. When the Excel Preferences window appears, click on the Ribbon icon in the Sharing and Privacy section. In the Customize section, check Developer in the list of tabs to show. Then click on the OK button. Select the Developer tab from the toolbar at the top of the screen. Then click on the Editor option in the Visual Basic group. Now the Microsoft Visual Basic editor should appear and you can view your VBA code.
Huge help and worked like a charm. Thanks for the timesaver! I still had to figure out how to add the developer tab on a Mac so for those people in my shoes (Excel >>> Preferences >>> View >>> Check box for Developer Tab).
thank you so much sir. it is very useful for me. i am very much tired to find it but nothing any video to teach me. your video is very useful and i done my work just in the few second. ❣❣❣❣❣
to make this function more usable return SubAddress as well Function URL(Hyperlink As Range) URL = Hyperlink.Hyperlinks(1).Address & Hyperlink.Hyperlinks(1).SubAddress End Function
When I use this, it extracts the URL without the # that breaks it. I then have to go back in and add the # back into the URLs. Is there a way to write it so the output either includes the # or force it by doing 1, # (forced in), 2?
@@KC-dz2bx I had the same problem - try: Function URL(Hyperlink As Range) URL = Hyperlink.Hyperlinks(1).Address & "#" & Hyperlink.Hyperlinks(1).SubAddress End Function
Good explanation. Please provide the video to fetch the different data from the source workbook by extracting URL based on pull down options selection & display it in a destination workbook. Thanks.
OMG a video that doesn't open with "funny" jokes, or long explanations that restate what the title of the video already explains the video is about, or doesn't have any long ass logo animations, and doesn't promise not to waste your time after first babbling for 45 seconds?! I'm literally just subbing in support of a creator who doesn't engage in any of that other nonsense, and I haven't seen the video yet! Thank you for GETTING TO THE POINT and respecting your viewers' time!!
This was THE BEST EVER video on "how to dos"! You were so informative and very helpful. I subscribed and will always check your sites out for help! Thank you.
Very informative and works perfect! The concise description of the what and why of the code is helpful for future work in VBA as well. Awesome job. Thank you.
very clear explanation - never would have figured out how to write custom function (I am surprised there is no standard fuction to do that) - I had to use the other tricks when autosave messed up all my links - saved me lots of time - thank you
Thank you, this almost worked for me, however, the hyperlink address returned stopped at a "#" within the hyperlink, about halfway through the address. Anyway to address this?
Thank you for sharing. Would you mind advise what is the meaning of index "(1)" at the end of object Hyperlinks? What if I use Hyperlinks(2) or Hyperlinks(0) or Hyperlinks() ? I can't find the related information from Google, so I wonder if you can tell me about it would be highly appreciate.
Hi, Thanks for the video, however, this does not solve my particular issue of hyperlink extraction. I have an Excel sheet that uses (from a friendly name in a cell) =HYPERLINK("URL of an external file","Friendly_Name") I need to extract the hyperlink from the cell to parse to another program as a link. I have tried the solution here but it returns a #VALUE! error. I assume this is because the =HYPERLINK is a formula and not just a HYPERLINK?
Yes, you are correct ... you may be able to use =FORMULATEXT(A1) and point to your HYPERLINK() formula cell. Then, fill that formula down the list. Then, in the top cell to the right of the first FORMULATEXT() cell, manually type in the first URL. Then hit Ctrl E (or Data > Flash Fill) and Excel should be able to detect the pattern and fill down the remaining URLs based on the FORMULATEXT results. Hope it helps!
Hi there, I feel a bit silly to ask this, but when I entered the formula nothing was returned. I can see the formula in the cell, and it didn't give me an error, but there is nothing showing in the cell. I checked silly things like making sure the font color isn't set to white. Any suggestions of why I can't see the URL? Just a bit more information, the link is to "a place in the document" and sends the user to a specific cell in another worksheet in the workbook. When I hover over the link I can see the URL address, but the formula you shared doesn't return this, just a blank. Thank you!
This is pretty much what I need, BUT, I want to go one step further and only extract only PART of the URL in to a column, namely some numbers that occur at the end. Is this possible, or will I have just have to extract the whole URL and delete the first part that I don't need?
Yes ... I would recommend extracting the entire URL, and then write a formula in the next column that extracts the numbers at the end of the returned URL. One function that could help is the RIGHT function. For example, if you wanted to extract the last five characters from the URL in A1, it would be =RIGHT(A1,5). Hope it helps! Thanks Jeff
I tried this, does not work. But I have worked with formulas before and it should. When I run this, it puts #VALUE! in the field. Can you see an error? Function GetUrl(Hyperlink As Range) GetUrl = Hyperlink.Hyperlinks(1).Address End Function
Ctrl+C Copy the hyperlinks (functions), like normal. Then go to your target sheet/workbook/column, and choose Edit > Paste Special and only choose to paste VALUES. Paste Special > Values converts function calculations into regular text.
Yes ... after you have the resulting text URL in a cell, you can write a formula next to it that uses the TEXTAFTER function like this =TEXTAFTER(A1,"#"). Hope it helps!
@@ExcelU Thanks, but afraid that doesn't work - just gives a #N/A. I don't think that the text after the # in the URL is extracted by the VBA routine. It is definitely there, i can see it when I hover over the original source. Any other suggestions, please?
@@mikereed2497 Oh yes, sorry about that ... to extract the text after the #, update the VBA code to use the SubAddress property instead of the Address property. In other words, change the VBA from "URL = Hyperlink.Hyperlinks(1).Address" to URL = "Hyperlink.Hyperlinks(1).SubAddress". Hope it helps! Thanks Jeff
Amazing tutorial - short and simple. No Nonsense
Thanks :)
By far, the best tutorial on this function. May I please add that for MAC users, the Developer tab may not be visible for them to open VB. And I honestly don't know what F11 is since when I hit Alt F11, it opened sound. So I found this advice to manually open VB on a Mac:
The Developer tab is the toolbar that has the buttons to open the VBA editor and create Form Controls like buttons, checkboxes, etc. To display the Developer tab, click on Preferences under the Excel menu at the top of the screen.
When the Excel Preferences window appears, click on the Ribbon icon in the Sharing and Privacy section.
In the Customize section, check Developer in the list of tabs to show. Then click on the OK button.
Select the Developer tab from the toolbar at the top of the screen. Then click on the Editor option in the Visual Basic group.
Now the Microsoft Visual Basic editor should appear and you can view your VBA code.
Thanks for sharing the Mac side of things ... I don't have Excel for Mac here so I appreciate your note!
Did this work for you? I am literally entering the exact same code as the video but I get #VALUE! message.
@@ExcelU just tried this on O365 for the Mac and it worked perfectly!
@@rodaleach Rod!! Thanks for the heads-up, glad to know the solution is mac friendly :)
Lifesaver! and thanks for getting straight to the point!
Huge help and worked like a charm. Thanks for the timesaver! I still had to figure out how to add the developer tab on a Mac so for those people in my shoes (Excel >>> Preferences >>> View >>> Check box for Developer Tab).
Thanks so much for sharing the Mac navigation :)
Saved me HOURS! I was doing CtrlK, CtrlC, Cancel, CtrlV for each cell ... I have close to a thousand to do.
This saved me HOURS. Thanks.
You saved dozens of hours for me! TY!
thank you so much sir. it is very useful for me. i am very much tired to find it but nothing any video to teach me. your video is very useful and i done my work just in the few second. ❣❣❣❣❣
Glad it helped you get your work done faster :)
This is awesome. Works great - thank you!
to make this function more usable return SubAddress as well
Function URL(Hyperlink As Range)
URL = Hyperlink.Hyperlinks(1).Address & Hyperlink.Hyperlinks(1).SubAddress
End Function
When I use this, it extracts the URL without the # that breaks it. I then have to go back in and add the # back into the URLs. Is there a way to write it so the output either includes the # or force it by doing 1, # (forced in), 2?
@@KC-dz2bx I had the same problem - try:
Function URL(Hyperlink As Range)
URL = Hyperlink.Hyperlinks(1).Address & "#" & Hyperlink.Hyperlinks(1).SubAddress
End Function
Good explanation. Please provide the video to fetch the different data from the source workbook by extracting URL based on pull down options selection & display it in a destination workbook. Thanks.
OMG a video that doesn't open with "funny" jokes, or long explanations that restate what the title of the video already explains the video is about, or doesn't have any long ass logo animations, and doesn't promise not to waste your time after first babbling for 45 seconds?! I'm literally just subbing in support of a creator who doesn't engage in any of that other nonsense, and I haven't seen the video yet! Thank you for GETTING TO THE POINT and respecting your viewers' time!!
Thanks for your kind note, and welcome to the channel :)
This was THE BEST EVER video on "how to dos"! You were so informative and very helpful. I subscribed and will always check your sites out for help! Thank you.
Thank you so much, and welcome aboard!
Do you know how to convert a large Excel list of URLs to an HTML file in order to make them Chrome bookmarks?
This absolutely saved my ass on a project. Thank you!!!
You just saved me a ton of work, thanks!
Glad it helped!
very nice, there are a lot of convoluted ways to do this posted on the web. This is sweet .
Exactly what I was looking for. Thank you.
Glad it was helpful :)
BRILLANT!!! you just saved me literally
hours doing this manually!!!
I'm glad it was helpful, and thanks for your kind comment :)
BLESS YOU! WOW! Me, too, me, too, me too! Saved HOURS of work with 2 min super simple explanation! THANK YOU!
Really helpful. Thanks so much!
Glad it helped :)
Very informative and works perfect!
The concise description of the what and why of the code is helpful for future work in VBA as well. Awesome job. Thank you.
Thank you for the tutorial! It was exactly what I needed, so helpful
Awesome video! Allowed me to do what I needed to do in minutes vs hours.
Thank you very much! It was helpful!
Glad to hear that!
Life saver, you helped a ton! I needed this for my powerbi Dashboard and it helped!
Awesome. Thanks very much. Jumped right in, and the video explains exactly what I needed. Subscribed immediately.
Glad it helped ... and welcome to the channel :)
Thank u so much sir
It saved my lot of time
Glad it helped!
Very helpful! 🥰
Thank you so much. It helped me.
Excellent video , to the point.
Glad you think so!
This is incredibly useful, and appreciate the work that you have completed! Well done!
Thank you, and I'm glad it was useful :)
Thank you Jeff! AMAZING... This save me so mucnh time w/ my work. Your instruction is easy to undersatnd and straightforward.
Thank you, this worked perfectly.
Thank you very much, it save a lot of time for me. Subscribed
Thanks ... glad it was helpful :)
You made life easy! Thank you!
Glad it helped!!
You're my hero
Thanks ... glad it helped!!
Terrific. This was very helpful. Thank you.
You're very welcome!
Great tutorial, thanks so much.
I'm glad it was helpful :)
Awesome!!! thank you so much!!
Glad it helped!
Thanks for sharing that saved valuable time. Much appreciated
Thank youuuu! Such a big helppppp!
Happy to help!
Thank you very much! Very useful for my SEO work
Glad it was helpful!
Great video!
Thanks!
Handy and helpful. Also thanks to the other comments about how to get dev open on Mac
Awesome tutorial!
Thank you!
Thank you sir!
Glad it was helpful :)
You saved my day man. Thank you so much.
Glad it helped :)
thank you. this video made my day.
very clear explanation - never would have figured out how to write custom function (I am surprised there is no standard fuction to do that) - I had to use the other tricks when autosave messed up all my links - saved me lots of time - thank you
Glad it helped ... thanks!
Super thanks. This is best way!!!
Glad it was helpful :)
This was so great!! thank you so much. 10/10
My man, thank you !
My pleasure :)
Work as charmed. Thank you for the tips!!
Helped a lot!!!!!!!!
Glad it helped ... thanks!
Thank you so much
Thank you, this almost worked for me, however, the hyperlink address returned stopped at a "#" within the hyperlink, about halfway through the address. Anyway to address this?
Nice one Jeff! Thanks for the tip. Thumbs up!!
Thanks Wayne!
This is an amazing function for sure!
Thank you so much!
Glad it helped! You're invited to get free Excel tips straight to your inbox, sign up here www.excel-university.com/subscribe/
Thank you!
Nice! Works fine. Thank's for help.
Can't thank you enough! 👍🖐
No worries ... and welcome :)
Thank you for sharing.
Would you mind advise what is the meaning of index "(1)" at the end of object Hyperlinks?
What if I use Hyperlinks(2) or Hyperlinks(0) or Hyperlinks() ?
I can't find the related information from Google, so I wonder if you can tell me about it would be highly appreciate.
Excellent stuff. Exactly what I needed! Thanks so much. I am now a subscriber 😊
very useful
Thank you :)
thank you very much!
You're welcome!
This was a huge time saver! Thank you so much!
You're welcome!
Very helpful thank you
Very welcome
Great learning and very helpful...Thanks.
Amazing, thanks :3
You explain really good
Thank you
You rock!
You are amazing! Thank you!
You're so welcome!
youre a gem
all heros dont wear capes!
Omg thanks!
Hi, Thanks for the video, however, this does not solve my particular issue of hyperlink extraction. I have an Excel sheet that uses (from a friendly name in a cell) =HYPERLINK("URL of an external file","Friendly_Name") I need to extract the hyperlink from the cell to parse to another program as a link. I have tried the solution here but it returns a #VALUE! error. I assume this is because the =HYPERLINK is a formula and not just a HYPERLINK?
Yes, you are correct ... you may be able to use =FORMULATEXT(A1) and point to your HYPERLINK() formula cell. Then, fill that formula down the list. Then, in the top cell to the right of the first FORMULATEXT() cell, manually type in the first URL. Then hit Ctrl E (or Data > Flash Fill) and Excel should be able to detect the pattern and fill down the remaining URLs based on the FORMULATEXT results. Hope it helps!
@@ExcelU Works, thank you!
I keep getting the #VALUE! error returned. Please help! I really need this function for a project.
Same there's no solution in the entire internet. Gemini chatgpt doesn't help
Thank you!!!!!!!!!!!!!
thanks a lot!!
No worries!
what is you need to save it as a csv for datatable.
saved me hours
when you create an add-in for this function you can use it all the time without opening or saving it as a macro-enabled workbook
Can you do it in bulk ?
Hi there, I feel a bit silly to ask this, but when I entered the formula nothing was returned. I can see the formula in the cell, and it didn't give me an error, but there is nothing showing in the cell. I checked silly things like making sure the font color isn't set to white. Any suggestions of why I can't see the URL? Just a bit more information, the link is to "a place in the document" and sends the user to a specific cell in another worksheet in the workbook. When I hover over the link I can see the URL address, but the formula you shared doesn't return this, just a blank. Thank you!
THANKS USE FULL FOR ME
THANKS LOT
Thank you so much - now I can die in peace 🙏😉
Super
Thanks :)
This is pretty much what I need, BUT, I want to go one step further and only extract only PART of the URL in to a column, namely some numbers that occur at the end. Is this possible, or will I have just have to extract the whole URL and delete the first part that I don't need?
Yes ... I would recommend extracting the entire URL, and then write a formula in the next column that extracts the numbers at the end of the returned URL. One function that could help is the RIGHT function. For example, if you wanted to extract the last five characters from the URL in A1, it would be =RIGHT(A1,5).
Hope it helps!
Thanks
Jeff
this is fantastic, but how to do this when the link is an image link?
When iterating through excel cells ,how to find cell value has hyperlink
I tried this, does not work. But I have worked with formulas before and it should. When I run this, it puts #VALUE!
in the field. Can you see an error?
Function GetUrl(Hyperlink As Range)
GetUrl = Hyperlink.Hyperlinks(1).Address
End Function
If I do this, I'm getting #Value! Error instead of hyperlinks
this does not work in my case. it displays the same hyperlink we try to decode...
I can't get this to work, I get #Value! - My hyperlink is within the cell A1 but the url is inside a sentence. Error says wrong data type.
Hello sir! What if I want to copy the hyperlinks to another excel sheet? Tried doing that but doesn't exactly work.
Ctrl+C Copy the hyperlinks (functions), like normal. Then go to your target sheet/workbook/column, and choose Edit > Paste Special and only choose to paste VALUES. Paste Special > Values converts function calculations into regular text.
Standard Hyperlinks work but Imported from a html table into excel, doesn't work.
I can't get this working on Mac excel, can anyone help? please
Works well, but I want to extract the fragment after the # symbol - any way to do that?
Yes ... after you have the resulting text URL in a cell, you can write a formula next to it that uses the TEXTAFTER function like this =TEXTAFTER(A1,"#"). Hope it helps!
@@ExcelU Thanks, but afraid that doesn't work - just gives a #N/A. I don't think that the text after the # in the URL is extracted by the VBA routine. It is definitely there, i can see it when I hover over the original source. Any other suggestions, please?
@@mikereed2497 Oh yes, sorry about that ... to extract the text after the #, update the VBA code to use the SubAddress property instead of the Address property. In other words, change the VBA from "URL = Hyperlink.Hyperlinks(1).Address" to URL = "Hyperlink.Hyperlinks(1).SubAddress".
Hope it helps!
Thanks
Jeff
@@ExcelU Brilliant, Jeff, just what I was after! Thank you for your kind help.
Best, Mike
@@mikereed2497 Excellent :)