SharePoint & Excel -Display full text of URL from Reclinks contained in an Excel Spreadsheet column

Question

Hi all,

I have a spreadsheet with 400 lines that has in one column links reclinks of emails created in SharePoint. I need to display the full text of each hyperlink.

I tried using this VBA in Excel, however it only displays the shortened reclink text.

Here is what is displayed when I use this VBA in Excel

This is what I would like to see (Column C is the most important)

Any advice is greatly appreciated. Thank you

Answer 1

Hi CMK_485,

Have you tried with "HL.SubAddress"

".SubAddress" returns or sets the location within the document associated with the hyperlink.

Hope it helps.

Best Regards,

Snow Lu

Answer 2

Hi Snow Lu,

Thank you for your reply. I am sorry, I am no familiar with those terms. Do you mean I should use one or both in the VBA? If yes, could you please type out what I should put in the VBA, so I can copy and paste it into a new module? (I have very little experience with VBA)

regards

Carolyn

Answer 3

Hi CMK_485,

Add this function in vba, then =getadrs(A1). ---A1 is the cell with hyperlink.

Let me know if you met any problem.

---

Function GetAdrs(Rng)

Application.Volatile True

With Rng.Hyperlinks(1)

GetAdrs = IIf(.Address = "", .SubAddress, .Address)

End With

End Function

---

Could you share the screen of the hyperlink (Right click the cell - > edit Hyperlink)? If the full Reclink not here, you should manual add the full Reclink details.

Answer 4

Thank you for your help. I have just been advised by a teammate that the hyperlink information is not required anymore. Bye for now and thank you again.

Answer 5

Thanks for your updates. I will close this case.

If you have any other questions, you can also post a new thread in our forum, and we will also try our best to help you.