Mass Edit Hyperlinks

Question

We use a phone call recording utility that exports an Excel file with a hyperlink to the recording on our phone server. Before we import these records into Salesforce we need to change the hyperlink from the text Call Link to the actual hyperlink. In Excel version 15 and before we had the ability to do this en mass using the Edit Hyperlink dialog shown below. We could blank the Text to Display field and all of the hyperlinks would be changed to there unique links.

I cannot find this feature in version 16 and wondered if there was a way to accomplish this task. We have too many hyperlinks to make the changes one at a time.

Answer 1

Hi RogerDotson

The first one was for a UDF which is basically an Excel Function that can be used in a Formula.

The below will allow for a look.

Sub GetFullURLLoop()

    Dim hypLink As Hyperlink

    Range("B2").Select

    For Each hypLink In ActiveSheet.Hyperlinks

        hypLink.Range.Offset(0, 1).Value = hypLink.Address

    Next

End Sub

Change         hypLink.Range.Offset(0, 1).Value = hypLink.Address value 1 to be how many cells to the right you want the result to go to.

Regards

Answer 2

Let know your thoughts on it this is what you want. If you have any more questions or it needs to be improved further let me know.

Regards

Answer 3

Then go back to you spreadsheet.

In a blank column type the following formula

=GetFullURL(A2)

Please that you cell reference may be different depending on how your spreadsheet is set out.

Drag down as per required.

Answer 4

Hi RogerDotson

I would recommend a UDF (User Defined Function), this can be generated in VBA.

1. Press Alt+F11, this will open the VBA Editor;
2. Right click the VBA Project then select Insert and then Module;
3. Paste the code in below:

Public Function GetFullURL(rngCell As Range) As String

On Error Resume Next

GetFullURL = rngCell.Hyperlinks(1).Address

End Function

Answer 5

Hello ROGERDOTSON

Welcome to the Microsoft community.

My name is Soraya F. and I am an independent consultant to help you!

Access this link and check the instructions in the "Edit Hyperlink" tab:

https://support.office.com/en-us/article/work-w...

I hope I have been helpful.

It is very important for our community to know if the answer was helpful to solve your problem!

Soraya F.