Last week I was set a challenge by a client. They had a list of URL’s which they had imported into Excel using Power Query. However, when the URL’s were loaded into the Excel worksheet they weren’t clickable.

The challenge was to convert text URL’s into clickable hyperlinks. I achieved this by creating a new custom column in Power Query using the following formula:

“=HYPERLINK(“”” & [Company_Website_URL] & “””)”

However even that didn’t solve the problem..

To convert the “formulas” into hyperlinks requires that each cell is put into Edit mode (F2 or double click) and then immediately (i.e. without making changes) Enter is pressed. Not wanting to do this on several hundred cells, I automated this with a macro.

If you want to see exactly how I rose to the challenge, check out the video below:

You can download a copy of the sample file and the macro by clicking through to YouTube. The link is in the YouTube description.

I’d like to say a big thank you to Imke Feldmann who runs the thebicccountant.com blog. It was during my research that I came across a post on her blog which helped me to solve my client’s problem. I used that blog post as a starting point, tweaking her solution and adding in the macro, which gave my client exactly what they needed.