Hello
I have some hyperlinks in my sheet which dynamically change.
So for example in A1 a formula might produce: #Sheet2!$B$90
and then in another cell a hyperlink uses this reference: =HYPERLINK(A1,"")
I'd like to create another link which is 10 rows up from the original and thought about using: =LEFT(A1,11)&(RIGHT(A1,2)-10), but this relies on the number being 2 digits and so fails miserably if it is greater than 99.
So my question please, if you have a character string that ends in a number (which will always be in the range 10-999), what is the most efficient way to subtract from that number, leaving the rest of the string unchanged?
I thought about testing the length of the string with IF & LEN, and then using (RIGHT(A1,2)-10) or (RIGHT(A1,3)-10), but is there a better way please?
Thanks
Robin
Bookmarks