+ Reply to Thread
Results 1 to 9 of 9

Extract right portion of a formula

  1. #1
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,178

    Extract right portion of a formula

    I was approached at work with a question about extracting a specific portion from a formula.

    Here is the formula which was hard coded at least for the last two portions of the equation: =J7+11+3

    Further example:
    =J7+11+3
    =J8+10+2
    =J9+9+4

    The user wanted to extract the last number so they could sum those values. In this example, 3, 2, and 4

    The only method that came to mind was text-to-columns with a find and replace.

    First I hightlighted the column and replaced the = with --
    Copied that column to a new column and split text by the + using text-to-columns

    Is there an easier method and/or, what if I just want to extract the +3, +2, +4, etc.,?
    Last edited by jeffreybrown; 12-23-2011 at 07:48 PM.
    HTH
    Regards, Jeff

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Extract right portion of a formula

    Will the right numeric always be a single digit or will it range to more digits?
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,178

    Re: Extract right portion of a formula

    It could be more than the single digit

  4. #4
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Extract right portion of a formula

    Well, after giving this a bit of thought, I think the Find/Replace > text-to-column operation is probably the best route to take.
    If this is something you will do often record a macro and post the code and sample workbook for help in refining the code.

    A Custom function could probably be written for this.

  5. #5
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Extract right portion of a formula

    You could develop this UDF
    Please Login or Register  to view this content.
    Then in Excel
    Please Login or Register  to view this content.
    [EDIT]
    This should allow for positive and negative situations e.g."=J9+9-4"
    Please Login or Register  to view this content.
    Last edited by Marcol; 12-23-2011 at 03:13 PM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,178

    Re: Extract right portion of a formula

    @ Palmetto,
    This is most likely a very limited need so the Find/Replace > text-to-column operation works fine. No need for a sledge hammer to fix this job

    @ Marcol,
    The UDF is a great addition also

    Thank you to both for your help...

  7. #7
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Extract right portion of a formula

    If the "delimiter" is always "+" then try this
    Please Login or Register  to view this content.
    [EDIT]
    See following posts.
    Last edited by Marcol; 12-24-2011 at 10:22 AM.

  8. #8
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Extract right portion of a formula

    You still have to first convert the source cells to a text string before using that formula.

    Assuming the Find/Replace operation is completed, you could use this array formula (building on Marcol's formula) to sum the cells.

    {=SUM(MID(SUBSTITUTE(A2:A100,"+","|",2),FIND("|",SUBSTITUTE(A2:A100,"+","|",2))+1,255)*1)}

  9. #9
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Extract right portion of a formula

    @ Palmetto
    You still have to first convert the source cells to a text string before using that formula.
    Quite right, I forgot that the cell contained a formula and not a string, my mistake ...

    If you have ,or can install the morefunc add-in you could use
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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