Forum Discussion

Andrew Lindsay's avatar
Andrew Lindsay
Copper Contributor
Feb 20, 2019
Solved

Issue with hyperlink formula when a space character is in the destination worksheet name

I'm using the following formula to create a hyperlink to another worksheet in the same workbook.  The formula parses out the destination worksheet from another cell (A12 in the example below) using t...
excel
Formulas and Functions
  • Twifoo's avatar
    Twifoo
    Feb 21, 2019
    Hi Andrew,
    Assuming your destination cell is A1, your hyperlink formula is:
    =HYPERLINK("#'"&RIGHT(A12,
    LEN(A12)-FIND("|",SUBSTITUTE(A12,"^","|",
    LEN(A12)-LEN(SUBSTITUTE(A12,"^","")))))&"'!A1","Link")
    To be safe, always enclose the Sheet Name in single quotes (') whether or not such Name includes a space.
    Cheers!
    Twifoo
Twifoo's avatar
Twifoo
Silver Contributor
Feb 21, 2019
Hello Andrew,
Please specify the value of A12 and the destination of your hyperlink. Note that the Sheet Name must be enclosed in single quotes (') whenever it includes a space.
Andrew Lindsay's avatar
Andrew Lindsay
Copper Contributor
Feb 21, 2019

A12 is a formula.  Below is the value:

Power^Smart-UPS - 6000VA - 208V to 208 - 4 OUTPUT / 1 INPUT - 6 RU^Cyberpower ^OL6KRT3UTAA^1^2014^8^2022^5652^0.03^7160^SH 110 Main

 

The destination worksheet is:

SH 110 Main

 

Thanks.

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Feb 21, 2019
    Hi Andrew,
    Assuming your destination cell is A1, your hyperlink formula is:
    =HYPERLINK("#'"&RIGHT(A12,
    LEN(A12)-FIND("|",SUBSTITUTE(A12,"^","|",
    LEN(A12)-LEN(SUBSTITUTE(A12,"^","")))))&"'!A1","Link")
    To be safe, always enclose the Sheet Name in single quotes (') whether or not such Name includes a space.
    Cheers!
    Twifoo
    • Andrew Lindsay's avatar
      Andrew Lindsay
      Copper Contributor
      Feb 21, 2019

      Thanks Twifoo.

       

      That's a clever implementation substituting the carat for the pipe to get the location of the last carat.  But I'm wondering what does it get me beyond using the MID function to parse out the worksheet name?  Just inserting the single quotes into my existing formula is shorter.  Am I missing something?

       

      PS. After your first post I tried using single quotes, but I put the second one after the exclamation mark instead of before.  Doh!

       

      This works too:

      =HYPERLINK("#'"&MID($A12,FIND(CHAR(1),SUBSTITUTE($A12,"^",CHAR(1),11))+1,31)&"'!A1",MID($A12,FIND(CHAR(1),SUBSTITUTE($A12,"^",CHAR(1),11))+1,31))

      • Twifoo's avatar
        Twifoo
        Silver Contributor
        Feb 21, 2019
        Your formula is shorter because 11, which is the last instance of the circumflex accent (^), is hard-coded. Thus, your formula is not dynamic. Conversely, my formula dynamically calculates such last instance.
        Also, 31 is hard-coded in your formula but I won’t object to that because the maximum length of a Sheet Name is nonetheless 31 characters.

Resources