Excel hyperlink to web page location with id or named anchor

Question

In Excel, I can create a hyperlink to a web page. However, if the URL contains a # character, Excel always converts the hash sign to space-hyphen-space so the link no longer works. The URL http://www.example.com/page#location becomes http://www.example.com/page%20-%20location

I have tried three ways

1. manually, right-click a cell, click "hyperlink" and type the URL in the address box

2. programatically, providing the full URL:

   Worksheets(1).Hyperlinks.Add Address:="http://www.example.com/page#location"

3. programatically, giving the location as a "subaddress":

   Worksheets(1).Hyperlinks.Add Address:="http://www.example.com/page", SubAddress:="location"

All three give the same problem. Is it possible to create a link to a specific location (id or named anchor) not just to a web page? I would be grateful for any help.

Note: the code examples are incomplete; for clarity, I have omitted the parameters Anchor and TextToDisplay.

Excel version: 2010 (14.0.6126.5003) SP1

Answer 1

This is not a browser problem. MS Office applications are mangling URLs with a # being replaced with ' - ' (the %20 is a space).

This annoying problem is admitted to exist by Microsoft themselves. To quote from that page:

These problems may occur when the name of file to which you create the hyperlink contains a pound sign (#).

NOTE: The pound sign is a valid character to use in a file name but is not accepted in hyperlinks in Office documents.

Their only workarounds are to remove the offending # or to copy and paste the URL from the document ... utterly ridiculous considering the reason most people would be using the # is to make it easier for users to find content.

Request that Microsoft improve this behavior by voting here: https://office365.uservoice.com/forums/264636-general/suggestions/32273917-stop-mangling-urls-containing-a-so-we-can-link-t