# Extract right portion of a formula

1. ## 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.,?

2. ## Re: Extract right portion of a formula

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

3. ## Re: Extract right portion of a formula

It could be more than the single digit

4. ## 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. ## 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.``

6. ## 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. ## 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.

8. ## 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. ## 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.``

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

#### 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