How to Display Full SharePoint Hyperlink URLs in Excel using VBA
Question details
The user needs to extract and display the complete underlying URL of a SharePoint hyperlink in an Excel cell, rather than the shortened display text.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Working with SharePoint hyperlinks in Excel where the display text hides the actual full web address.
- Observed behavior
- Excel shows a shortened or friendly display name instead of the full URL string, making it difficult to read, extract, or copy the actual SharePoint web address directly from the cell text.
Ensure your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Trust Center settings so the custom function can run correctly.
Use a VBA User-Defined Function to Extract the URL
Create a custom VBA function to read the underlying hyperlink address from a cell and display it as plain text.
By default, Excel does not have a native formula to extract the underlying URL from a hyperlink. This method uses a custom VBA script to extract the actual web address hidden behind the hyperlink's display text. Once the function is added to your workbook, it can be used exactly like any normal Excel formula.
Press ALT + F11 on your keyboard while in Excel to open the Visual Basic for Applications (VBA) window.
Click 'Insert' in the top menu bar of the VBA window and select 'Module' to create a blank script window.
Copy and paste the following code into the module window: Function GetAdrs(Rng) Application.Volatile True With Rng.Hyperlinks(1) GetAdrs = IIf(.Address = "", .SubAddress, .Address) End With End Function
Close the VBA editor and return to your worksheet. In an empty cell, type =GetAdrs(A1) (assuming cell A1 contains your SharePoint hyperlink) and press Enter to display the full URL.
Extract Hyperlinks Easily in WPS Spreadsheet
WPS Office Spreadsheet provides full support for VBA macros and custom user-defined functions, allowing you to easily extract SharePoint URLs and manage complex spreadsheet tasks just like you would in Microsoft Excel.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing your SharePoint hyperlinks.
- 2. Access the VBA Editor: Navigate to the 'Tools' tab on the ribbon and click on 'Macros' followed by 'Visual Basic Editor'.
- 3. Insert Module and Code: Click 'Insert' > 'Module' in the editor, paste the GetAdrs VBA code provided in the solution, and save.
- 4. Apply the Custom Formula: Return to your sheet and type =GetAdrs(A1) in a target cell to extract the underlying URL effortlessly.
Frequently Asked Questions
Why does Excel shorten my SharePoint links?
Excel automatically displays a 'friendly name' or shortens long URLs to keep the spreadsheet visually clean, prevent text from overflowing into adjacent cells, and make the document easier to read.
Can I extract hyperlinks without using VBA?
Native Excel formulas do not currently have a built-in function to extract hyperlink addresses. You either need to use a VBA custom function or manually edit the hyperlink (Ctrl + K) to copy the web address.
Why does the =GetAdrs() formula return a #NAME? error?
This error occurs if Excel does not recognize the function. Make sure you have placed the VBA code in a standard Module (not a sheet module), saved the file as a Macro-Enabled Workbook, and enabled macros in your Trust Center settings.
Does this VBA script work for links to local files as well?
Yes, the script checks both the .Address and .SubAddress properties. It will successfully extract the underlying path for web URLs, SharePoint links, and local document hyperlinks.
