Forum Discussion
Issue with hyperlink formula when a space character is in the destination worksheet name
- 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
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.
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
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))
- 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.Ah. I could see the value of it being dynamic if all my other parsing were dynamic. But you'll note that I have 12 elements in the string. The worksheet name only happens to be the last. I could search for the nth instance of the carat to make the formula dynamic for all the parsing from the middle, but the the string isn't ever going to change so I just didn't see the value of bothering (or making the formula longer).