Sunday, 24 March 2019

macros - How to turn hundreds of text URLs in Excel into clickable hyperlinks?


How do I turn a column of urls into hyperlinks, so that users can click on the url in a cell and have it open in a browser?


I pasted 100 urls and each went into its own cell. When I double-click in the cell and then leave it, Excel turns the text blue and makes a link out of it. I don't want to double-click a hundred times, but still want to format all the cells into links.



Answer



The function in Excel for doing a hyperlink is =Hyperlink("http://www.techonthenet.com","Tech on the Net") where "http://www.techonthenet.com" is the internet address and "Tech on the Net" is the title that appears in the Excel cell.


Thus when you are writing the urls into the Excel file just wrap this function around each url. If you don't want to come up with a dynamic name you can always put the url as the name too.


If you aren't inserting the values programmatically then this site mentions using the HYPERLINK worksheet function. Though a even better reference is this which walks you through how to add a macro to excel and they supply the code for the macro. Thus when after you add this macro you can select the column of urls and run the macro and it converts the whole column into hyperlinks


No comments:

Post a Comment

How can I VLOOKUP in multiple Excel documents?

I am trying to VLOOKUP reference data with around 400 seperate Excel files. Is it possible to do this in a quick way rather than doing it m...