+ Reply to Thread
Results 1 to 5 of 5

Subtracting from a string that ends in a number?

  1. #1
    Registered User
    Join Date
    02-18-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    57

    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

    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

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,140

    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: copy to clipboard
    Please Login or Register  to view this content.


    This would be shorter using Excel 365:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    871

    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.

  4. #4
    Registered User
    Join Date
    02-18-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    57
    Very helpful replies which all worked so problem solved!

    Thanks

    Robin

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,140

    Re: Subtracting from a string that ends in a number?

    If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] IF number ends in 21 & % is <100
    By matrixpom in forum Excel General
    Replies: 5
    Last Post: 11-10-2021, 07:22 PM
  2. [SOLVED] Find string and return both ends of either side
    By ScabbyDog in forum Excel General
    Replies: 3
    Last Post: 03-28-2018, 05:31 AM
  3. [SOLVED] delete those parts of string which start with x and ends with y
    By wali in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-29-2018, 07:56 PM
  4. Replies: 3
    Last Post: 04-27-2016, 05:18 AM
  5. [SOLVED] Setting a range that ends where data ends?
    By Mgassma in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-02-2013, 09:46 AM
  6. [SOLVED] Check if string ends with one of the strings from an array
    By alienss in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-15-2013, 02:58 PM
  7. the ' symbol ends the SQL string, how do you replace it Excel
    By Valori in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-02-2006, 09:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1