16

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

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Graham
  • 316
  • 1
  • 2
  • 7
  • That's curious. I'm not getting that problem. I have WinXP with Excel 2007. I suspect there's a setting somewhere in options that might make a difference. In particular, check the auto-correct options for a "#". – D_Bester Jul 15 '13 at 14:39
  • Thanks for the suggestion. I've checked the auto-correct options, but can't find anything that would do this. – Graham Jul 15 '13 at 15:30
  • Appears to happen with Excel and Word 2013 as well. Very strange. – Mansfield Dec 27 '13 at 20:44
  • as alternative, you can try the Excel formula `=Hyperlink("http://www.example.com/page#location")` – Slai Aug 23 '16 at 20:44
  • Refer to closely related question on Super User, [MS Word 2013 - Hyperlinks with anchor tags (aka hash tags, bookmark tags) to external html files](http://superuser.com/questions/596414/ms-word-2013-hyperlinks-with-anchor-tags-aka-hash-tags-bookmark-tags-to-ext). – JohnC Sep 20 '16 at 13:59
  • Not a browser issue, its by design. Vote for Microsoft to improve this behavior here: https://office365.uservoice.com/forums/264636-general/suggestions/32273917-stop-mangling-urls-containing-a-so-we-can-link-t – QA Collective Nov 16 '17 at 00:53

9 Answers9

6

It appears to be a browser problem not an Excel problem. Switching from Google Chrome to Internet Explorer solved it.

Thanks everyone for your suggestions ... just saying that you didn't find the same problem was what prompted me to look elsewhere.

Graham
  • 316
  • 1
  • 2
  • 7
  • 2
    ... and changing my default browser back to Chrome did not reinstate the error. The spreadsheet was unchanged, but the behaviour differed. Anyway, problem solved, but I'm no wiser! – Graham Jul 16 '13 at 09:09
  • 2
    I've got the same issue, except that I can reliably reproduce it by setting Chrome as my default browser, and reliably fix it by setting IE as my default browser. Very cross with Microsoft at the moment! – RB. Nov 01 '13 at 14:17
  • This is not a browser issue. This is one is courtesy of Microsoft and 'by design' (sigh). See my answer here: (https://stackoverflow.com/a/47319383/5196274). 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) – QA Collective Nov 16 '17 at 00:51
6

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

QA Collective
  • 2,222
  • 21
  • 34
1

I was able to get around this by using %23 in place of the # (hashtag|pound sign). My URL's now work within Excel 2013. The 'hovertext' shows the # symbol in the URL, but editing the link shows the %23.

http://www.example.com/page#location does not work

http://www.example.com/page%23location works

JRHelgeson
  • 53
  • 2
  • 3
    Didn't work for me. Tried `%23` and `+%23` but both gave the error in Word Professional Plus 2010 `Unable to open... Cannot download the information you requested` before it got to my default browser, Chrome. – sgryzko Feb 10 '16 at 12:04
1

The best solution we have found, without manually changing registry entries, is to reset Internet Explorer to be the default browser. This resets the url, protocol and extension associations and resolves the issue.

Once you do that, even changing the default browser to Firefox and changing it back to IE will not break it again.

This solution is "cleaner" than playing around the registry and has been logged as the official solution at our company.

Martin Sauriol
  • 11
  • 1
  • This is the only solution that worked for me. Using IE as the default browser was not an option. But changing the default browser back to Firefox kept the link working properly as you stated. – Maxter Aug 23 '18 at 13:14
1

Unfortunately, in Excel, I still ran into issues with safe URL encoded hyperlinks.

They would work once, then changed to a file:// hyperlink after you clicked on it once, and was broken again.

So I used the =Hyperlink() function and all was good because Excel can't change my concatenated URL string in the formula.

Yay, I win...nope

When files are in protected view, due to security settings or if the user is previewing the file as an attachment in Outlook, the # symbol still gets turned into %20-%20

enter image description here

enter image description here

Since our IT department doesn't allowed us to install another browser, we cant make Internet explorer the default browser (since it already is).

Comparing two PCs, one that I was able to change the default browser vs a standard load, I noticed that the following registry entry was missing from the standard load.

Solution: Save as Office Hyperlink Fix.reg (and double click) 

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Windows\Shell\Associations\UrlAssociations\http]

[HKEY_CURRENT_USER\Software\Microsoft\Windows\Shell\Associations\UrlAssociations\http\UserChoice]
"Progid"="IE.HTTP"

After adding it to the standard load (required per every user) the links worked without any issues!

It's not all the registry entries required, but it was enough to get IE to ask to be the default browser again and add the rest (HTTPS...).

Profex
  • 1,370
  • 8
  • 20
0

You can create a hyperlink in a cell just by typing in the URL and hitting enter. Excel will recognize it as a URL if it has things like

".com"

and/or

"http://"

I tried your problem and didn't get it. Maybe there is some other problem with your URL. You might want to post a more similar URL to the one you are actually using(same character types)I have excel 2010 as well.

Stepan1010
  • 3,136
  • 1
  • 16
  • 21
  • Thanks, Stepan. I tried your suggestion. The URL shows correctly in the page but still goes to the wrong address when clicked. If you're not getting the same problem, I wonder if this is a version problem ... will investigate further. – Graham Jul 15 '13 at 15:38
  • His question is about how to include the bookmark "#" symbol in an Excel URL in such a a way that it survives the trip to the browser (without getting converted to "%20-%20") – Scott Fletcher Mar 13 '18 at 15:23
0

I had a coworker with this exact issue (including the %20-%20 error) in Word 2010 instead of Excel.

Solved the following way:

In Internet Explorer:

  • Internet Options
  • Programs tab
  • HTML Editor: ensure this is not blank
Unheilig
  • 16,196
  • 193
  • 68
  • 98
Matthew B Milton
  • 1
0

If the problem concerns links to the Share Point Server portals you can cut part of the hyperlink (in my case bolded part: Beginning_of_the_address /_layouts/15/start.aspx# /End_of_address).

For me works fine (MSO 16 + SPS 2k16).

Czymaupy
  • 1
0

I see this is an old post so perhaps we all know the answer but I had this issue and resolve it. I am using Excel 2016 so perhaps Ms team made the fix. I simply added the # symbol in the URL string and then used the function .SubAddress to add the bookmark to the URL. e.g.

Dim oCell as Range("A1") 'Change to correct cell range containing the Hyperlink
ThisWorkbook.followHyperLink oCell.HyperLink(1).Address & "#" & 
oCell.HyperLink(1).SubAddress
Xander81
  • 55
  • 1
  • 1
  • 9