Extract URL from Hyperlinks from a SharePoint List using Excel

Scenario: In a SharePoint Custom list, in one of the column, it stores the title of the SharePoint Sites as a Hyperlink, however, there is no column to store the URL. There were about 1000's of records of such type in this custom list. Now that the client is requesting to get the URL's of each of the Title of the SharePoint Site.

Resolution: This can be acheived by exporting the data to excel and writing a macro and running it.

Restriction: This is a manual process and will be helpful for getting monthly data kind of requirements. Automation for retreiving this data is out of scope in the blog.

Solution:
Follow the below steps to extract the URL's from the Hyperlinks in a SharePoint Custom List.

Step 1: Browse the SharePoint Custom list and select "Actions -> Export to Spreadsheet". Now that Save the file in Excel format. Now try to create a column to extract and save the URL's beside Hyperlink column in which the titles are stored.

Step 2: Now in Excel menu, click on Developer, and select Visual Basic. Browse the tree in the left menu of the VBA Project and double click on the sheet name in which the current data is saved.

Step 3: Add the below code and save and click on Play button to run the macros and then close this VB xlsx.

Sub ExtractHL_AdjacentCell()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value =
HL.Address
Next
End Sub


NOTE: Make sure that the macros is enabled in Excel. This can be checked from Excel menu -> Developer -> Macro Security and modify the Macro settings as needed.

You will now see all the extracted URL's in your Excel.


Step 4: Now copy the data and paste it in SharePoint list by creating a new column to store the URLs.



Date Changes, Month Changes, Year Changes, Be Yourself, No Matter What ever Changes......!