Extract right portion of a formula

[jeffreybrown]

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

[Palmetto]

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

[jeffreybrown]

It could be more than the single digit

[Palmetto]

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.

[Marcol]

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.

[jeffreybrown]

@ 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...

[Marcol]

If the "delimiter" is always "+" then try this

Please Login or Register  to view this content.

[EDIT]
See following posts.

[Palmetto]

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)}

[Marcol]

@ 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.