none
Hyperlink containing # in Excel RRS feed

  • Question

  • I understand that there is an issue having hash tags in hyperlink URLs within Excel spreadsheets.  When the link is followed and IE is opened, they are changed to %20 - %20, which breaks the URL. However, the work-arounds that I have found won't work for my scenario.  Our web application generates an Excel Spreadsheet for download listing many documents that the user has searched for and provides hyper links to the documents as well as a search UI for each document to search our site on some of the fields in the listing. The problem is, 1 character in the URL has to be a # for the hyperlink to execute the search. 

    I have tried to change the # to a %23, but it doesn't work.  If I paste the URL into the browser's address bar, replacing # with %23, the page load fails.

    Is there another way around this hashtag/Excel issue?  Or is there something that I can do to make IE recognize %23 as a hashtag?

    Thanks!


    KJ Dietrich

    Web Developer

    Also: Not exactly an Excel question, but I wanted to add that when Excel does this, replaces # with %20 - %20, and the hyperlink is followed, the web server (IIS)'s app pool memory usage blows up and the app pool restarts.  Still investigating that, but has anyone seen this behavior?  Thanks.
    • Edited by KJ Dietrich Friday, October 9, 2015 3:43 PM Additional Information
    Thursday, October 8, 2015 9:01 PM

Answers

  • David,

    Thanks for responding.  I have read the %23 method on other forums but for some reason, it does not work when I am using Chrome, Firefox or IE and following a hyperlink from Excel.  There may be some type of group policy at play on my environment.  I did come up with a solution however:

    I created a redirect page with some javascript.  The redirect page is in a completely public area, in our case wwwroot.  All of the hyperlinks have been modified to start with

    http://www.ourpage.com/DocumentRedirect.html?page=(restofurl)

    The javascript takes ?page=....  and replaces the %23 with # to fix the hashtag problem.  It also checks the useragent of the client browser.  If the user agent has "Protocol Discovery" in it, then it doesn't redirect.  If it doesn't have this string, then it redirects to http://www.ourpage.com/(restofurl)

    If the user isn't logged in, then they will be redirected to the login page, if they are they are forwarded directly to the URL intended. 


    KJ Dietrich Web Developer

    • Marked as answer by KJ Dietrich Wednesday, October 14, 2015 7:45 PM
    Wednesday, October 14, 2015 7:45 PM

All replies

  • >>>When the link is followed and IE is opened, they are changed to %20 - %20, which breaks the URL.

    This is a known issue with MS Excel. Basically, the hash/pound (#) sign is a valid character to use in a file name but is not accepted in hyperlinks in Office documents. The conversion to %20-%20 appears to be by design.

    For more information, click here to refer about You cannot use a pound character in a file name for a hyperlink in an Office program

    >>>Is there another way around this hashtag/Excel issue?  Or is there something that I can do to make IE recognize %23 as a hashtag?

    I have tested this around by using %23 in place of the # (hashtag|pound sign). URL's now work within Excel 2013.
    http://www.example.com/page%23location

    So I suspect that it is possible about a browser problem. Could you switch browser to reproduce this issue?
    Otherwise Could you check your URLs that have replaced the # (hashtag|pound sign) using %23 is correct?


    Sunday, October 11, 2015 1:29 PM
  • David,

    Thanks for responding.  I have read the %23 method on other forums but for some reason, it does not work when I am using Chrome, Firefox or IE and following a hyperlink from Excel.  There may be some type of group policy at play on my environment.  I did come up with a solution however:

    I created a redirect page with some javascript.  The redirect page is in a completely public area, in our case wwwroot.  All of the hyperlinks have been modified to start with

    http://www.ourpage.com/DocumentRedirect.html?page=(restofurl)

    The javascript takes ?page=....  and replaces the %23 with # to fix the hashtag problem.  It also checks the useragent of the client browser.  If the user agent has "Protocol Discovery" in it, then it doesn't redirect.  If it doesn't have this string, then it redirects to http://www.ourpage.com/(restofurl)

    If the user isn't logged in, then they will be redirected to the login page, if they are they are forwarded directly to the URL intended. 


    KJ Dietrich Web Developer

    • Marked as answer by KJ Dietrich Wednesday, October 14, 2015 7:45 PM
    Wednesday, October 14, 2015 7:45 PM
  • In case someone might find the JavaScript useful:

        <script type="text/javascript">
            function getURL(key) {
                key = key.substr(6)
                key = key.replace("%23", "#")
                return key;
            }
            function redirectURL(newPath) {
                var userAgent;
                userAgent = navigator.userAgent
                if (userAgent.indexOf("Protocol Discovery") < 0) {
                    window.location = newPath
                }
            }
            redirectURL(window.location.protocol + "//" + window.location.host + "/" + getURL(location.search))
        </script>


    KJ Dietrich Web Developer


    • Edited by KJ Dietrich Wednesday, October 14, 2015 7:48 PM
    Wednesday, October 14, 2015 7:47 PM
  • My workaround is to create a shortcut to the URL or filename/path that contains '#' and create a hyperlink to the shortcut. Sometimes though, excel is too clever by half and finds the ultimate target in the shortcut and links to that. I sometimes have to type in the shortcut address including the .lnk extension.
    Friday, January 19, 2018 10:24 AM
  • I found solution for this. You may use formula HYPERLINK for cell together with ENCODEURL and it's working!

    =HYPERLINK(ENCODEURL("https://www.w3.org/TR/html4/sgml/entities.html#h-24.4.1"), "Click for open link")

    • Proposed as answer by Vlad.Net Monday, February 19, 2018 4:57 PM
    • Unproposed as answer by Vlad.Net Monday, February 19, 2018 4:57 PM
    • Proposed as answer by Vlad.Net Monday, February 19, 2018 4:59 PM
    Monday, February 19, 2018 4:56 PM
  • Vlad, thank you!  This worked for me!
    Friday, June 26, 2020 8:19 PM