Convert all URLs in column to hyperlinks

**Stuartzz** · 01-09-2019, 01:48 AM

Hi there
I have the following code in a macro which converts worksheet/cell references in cells to hyperlinks. It does it for cells in column H that are in row 3 or below and that are populated. It works great.

Can someone please help me to adapt the code for another purpose so that each hyperlink is created from a URL in the cell, instead of being created from a worksheet/cell reference in the cell? The text for display should be simply the URL.

Any help is greatly appreciated.
Cheers, Stu

Please Login or Register  to view this content.

**Keebellah** · 01-09-2019, 03:49 AM

On the first read I thought I'm sure YOU understand what you're asking (I hope so )
But... reading it through (several times) I think I got it:
You do not want the cell to show the entire URL after converting it to a link but yous a descriptive text.
What you can do do is add variable and extract a porting of the RUL text before linking it and that is the text you place in TextoDisplay

Please Login or Register  to view this content.

Something like that, play around with it.

**Stuartzz** · 01-09-2019, 05:52 PM

Hi Keebellah
Apologies for lack of clarity in my request.

I just wanted to convert URLs to hyperlinks and have the URL as the text to display.

The code is below and I thank you for taking the time to assist me.

Cheers, Stu

Please Login or Register  to view this content.

**Keebellah** · 01-09-2019, 06:57 PM

The code you're passing does just that.
In cell I whatever the cell is set to hyperlink and the text displayed.
I think you'll need to be clear about what you really want.
Or add a sample with what you have and what you would like to see.

**Stuartzz** · 01-09-2019, 09:26 PM

Sorry, I know the code works. I meant to say that, my oversight. yes sample workbooks good, i usually do. should've this time as well.

thanks again

**Keebellah** · 01-10-2019, 03:20 AM

Okay, clear, I just thought that you wanted the display text to be more explicit, it now shows the enrite url so also the column width is greater.

If you extract part of the url like lets say the the text after the www and show the a portion of what follow like the first or second backslah your text is shorter

so instead of

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**Stuartzz** · 01-10-2019, 08:14 AM

Oh I see what you mean. Okay thanks I will have a play around with that code that you provided above. Some of the hyperlinks are indeed very long because they are SharePoint links, so that would be useful. Much appreciated :-)

**Keebellah** · 01-10-2019, 11:34 AM

Just to illustrate it
I've attached a file with one url in I fully written out (it's repeated in L just to copy back)
If you press the smiley the macro you sue runs and will convert I to hyperlink and the text to display as I explained.
Edit at will and you'll make it completely suitable for anything.