Hyperlink changes from # to - when clicked in Ex

Question

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

Want to improve this question? Update the question so it's on-topic for Stack Overflow.

Closed 2 years ago.

Improve this question

I've got a hyperlink in an Excel 2013 sheet that links to an internal website. When I right click and select "edit hyperlink", I see this in the address bar (which is correct):

https://myserver.company.com/home/default.html#article?id=1203291003

However, when I left click, middle click, or right click -> open hyperlink I get the same behavior: IE11 opens and I get a http 404 error because the link (shown below) is not found.

https://myserver.company.com/home/default.html%20-%20article?id=1203291003

What could be converting the # to %20-%20? This is very odd because %20 is a space and there are no spaces in the URL.

Answer 1

Workaround: Save as PDF

If for clients, read-only & don't need it in excel/Word format you could save file as a PDF. hyperlinks with "#" will work in PDF readers. Eg: http://help.overdrive.com/#videos?ade

Answer 2

I had one user who could not open linked websites containing a # in the URL using IE. She could open with Chrome and Firefox. Others could open with IE, Chrome and Firefox.

I investigated the http_user_agent value my version and found this: HTTP_USER_AGENT=Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; WOW64; Trident/6.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; InfoPath.3)

I found her version to have this value:

HTTP_USER_AGENT=Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.1; WOW64; Trident/6.0)

The key phrase is compatible; MSIE 10.0 (mine reads compatible; MSIE 7.0). When I opened IE Developer Tools, I discovered that the default Document Standards setting in my browser was IE7 Standards. The default setting in her browser was IE10 Standards. When she changed her document settings to IE7 Standards, closed the browser and then clicked the link from Excel, the page opened correctly in IE.

In our corporate environment, we use IE as the default browser, and we have Compatibility View turned on by default, but somehow her settings had been changed. It does give fair warning that in the future the hash (#) might be problematic in a URL.

Answer 3

This did not work for the machines I tried it on. The item below or process change on my end corrected the problem.

In MS Word hyperlinks, # becomes %. This is what is happening to me while running IE 11 on 64Bit Win7 OS. Word documents with hyperlinks that have the # symbols are causing the problem I believe.

Try a hyperlink from a word document that fails with error 404 like the following: http://iservice.prv/eng/imit/catalogue/software_application_service.shtml#outlook_text

Now change the # symbol near the end to a / then the hyperlink opens in IE 11 from a word document as expected like the corrected hyperlink below. http://iservice.prv/eng/imit/catalogue/software_application_service.shtml/outlook_text

When we are using the self-serve site and its content for support spec updates, make sure you change any links with # in them to / slashes before using/coping the link as an embedded hyperlink in the support spec word doc.

Answer 4

Whenever Excel sees a '#' sign in a hyperlink, it tries to interpret it as sort of a relative reference or a 'magic hash'. See this: http://www.myonlinetraininghub.com/excel-factor-18-dynamic-hyperlinks-and-the-magic

But this is bad if your hyperlink has a '#' sign in it without you intending to use this fancy Excel functionality. What can you do?

It looks like Excel interprets the '#' as a 'magic #' whenever Excel creates hyperlinks. But if you create the hyperlink within Excel's object model, or modify the address of an existing hyperlink in Excel's object model, you can foist an un-magical # into Excel, or MS Word, or any other MS Office application that would normally have a problem.

I made some Excel code that does this automatically for a sheet containing column A full of pound-sign-containing addresses (the text only), which fills column B with new working hyperlinks. I was able to paste these hyperlinks successfully into Word and Outlook.

Sub Make_Column_A_into_hyperlinks_hashmarkworkaround()

    'Column "A" contains the text of links to some files.
    'This subroutine will turn those cells into Excel hyperlinks...
    '...and then correct the hyperlinks in case Excel misinterpreted
    'a # mark which had originally occurred in the filename.
    'Because I'm using selection.End(xlDown).Select to find the complete list,
    'this program does not tolerate any blanks in the "A" column list.
    'Note that I don't add a hyperlink in Row 1.

    Dim A As Object
    Dim lngRow As Long
    Dim Height As Long

    With ActiveSheet
        .Cells(1, 1).Select
        Selection.End(xlDown).Select
        Height = Selection.Row

        For lngRow = 2 To Height
            .Hyperlinks.Add Anchor:=ActiveSheet.Cells(lngRow, 2), Address:=Chr(34) & _
                ActiveSheet.Cells(lngRow, 1).Value & Chr(34), TextToDisplay:="Open", _
                ScreenTip:=Chr(34) & ActiveSheet.Cells(lngRow, 1).Value & Chr(34)
        Next

        For Each A In ActiveSheet.Hyperlinks

            If A.ScreenTip <> "" Then
                If InStr(1, A.ScreenTip, "#") <> 0 Then
                    A.Address = Mid(A.ScreenTip, 2, Len(A.ScreenTip) - 2)
                End If
            End If

        Next
    End With

End Sub

Answer 5

There is a workaround:

Change the default value "htmlfile" of HKEY_CLASSES_ROOT.htm key by another value, i.e "IEhtml".

This answer is based on the following article and worked for me in MS Escel 2010: http://answers.microsoft.com/en-us/office/forum/office_2013_release-word/word-2013-hyperlink-converting-to-20-20/4e8a2e8d-b889-4c77-8276-551b11e296d4

Answer 6

I believe this might be of help to some people so i said id post it: I had the same problem with Word and as a work around i used a URL Encoder Do a quick search for one there's plenty such as here :

http://www.w3schools.com/tags/ref_urlencode.asp

Simply paste the url ,it will give back the Url that will work in a hyperlink from Word Excel etc.