# Subtracting from a string that ends in a number?

1. ## Subtracting from a string that ends in a number?

Hello

I have some hyperlinks in my sheet which dynamically change.

So for example in A1 a formula might produce: #Sheet2!\$B\$90

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  Register To Reply

2. ## Re: Subtracting from a string that ends in a number?

This will build a reference 10 rows above the original reference, regardless of how many digits the row number is.

Formula:  `Please Login or Register  to view this content.`

This would be shorter using Excel 365:

Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: Subtracting from a string that ends in a number?

If there will always be 11 characters before ending in a 2 or 3 digit number then you could use:

=LEFT(A1,11)&MID(A1,12,3)

The MID function just ignores any "extra" characters at the end.  Register To Reply

4. Very helpful replies which all worked so problem solved!

Thanks

Robin  Register To Reply

5. ## Re: Subtracting from a string that ends in a number?

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 