Convert multiple links to clickable text (or buttons)

[DenisVA]

I have a worksheet that contains a large number of internet links:

- All links are in one column
- One link per cell
- Around 2000 rows

I want to convert all of them into the same clickable text (Click Here) – or clickable buttons (with Click Here label).
For example:

Current Cell A2
Google Advanced Search

Desired Cell A2
Click Here (hyperlink)/or button

Current Cell A3
https://search.yahoo.com/search/options?fr=fp-top&p=

Desired Cell A3
Click Here (hyperlink) /or button

And so on…

I know how to do it one by one (Insert – Link – Text to Display) – but I just have too many.

I am also aware about Hyperlink function but it is not an option in this case (I cannot have more than one column for the links).

I use Excel 365

Is there any way that all links can be done in one hit – either with VBA or formula?

Thanks in advance.

 

[dmt32]

Hi,
Place following code in standard module & see if does what you want

Sub ChangeHyperlinkText()
    Dim i As Long
    For i = 1 To ActiveSheet.Hyperlinks.Count
        ActiveSheet.Hyperlinks(i).TextToDisplay = "Click Here"
    Next i
End Sub

The sheet with the hyperlinks must be the active sheet.

Dave

 

[DenisVA]

Hi Dave

Thank you very much for your response.
And my apologies for a delay in my response – I have been away several days with a minimum access to internet.

Your code works perfectly on the whole worksheet.

However, as I wrote above, I have links in one column (at this stage - A2:A1920) – these links need to be converted to clickable text (“Click Here”)
What I missed to mention is that I also have another column with a combination of links and text – this one need to stay as is.

So, is there any way that you code can be applied to a specific range only (in this case A2:A1920)?

Regards
David

 

[dmt32]

Hi,
try this update and see if does what you want

Sub ChangeHyperlinkText()
    Dim i As Long
    With ActiveSheet
        For i = 1 To .Hyperlinks.Count
            If Not Intersect(.Hyperlinks(i).Range, .Range("A2:A1920")) Is Nothing Then
               .Hyperlinks(i).TextToDisplay = "Click Here"
            End If
        Next i
    End With
End Sub

Dave

 

[DenisVA]

Hi,
try this update and see if does what you want

Sub ChangeHyperlinkText()
    Dim i As Long
    With ActiveSheet
        For i = 1 To .Hyperlinks.Count
            If Not Intersect(.Hyperlinks(i).Range, .Range("A2:A1920")) Is Nothing Then
               .Hyperlinks(i).TextToDisplay = "Click Here"
            End If
        Next i
    End With
End Sub

Dave

Thanks Dave
Works perfectly
Regards
David

 

[dmt32]

Most welcome - appreciate your feedback

Dave