Formula on text string extractoin

[mikepalerski]

Hello,

I want to sum two cells that extract the text string between two characters, "(" and ")" in adjacent cells. It appears that the SUM function will only return zero as a result because the results in the two cells in question are derived from extracting the text from adjacent cells rather than numbers I have directly imputed.

Is there a way to make the SUM function work properly on cells such as these?

[Fluff13]

Try adding +0 to the end of the formula in both D3 & D4

[dosydos]

use =VALUE() around your entire formula in D3 and D4 and then you can sum

[Glenn Kennedy]

Providing your small sample is representative and you don't have more than a few thousand rows... try:

=SUM(FILTERXML("<A><B>"&SUBSTITUTE(SUBSTITUTE(A2:A3,"$","</B><B>"),")","")&"</B></A>","//B[2]"))

Refer to the file. If the formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Glenn Kennedy]

or... if these functions are available in your Excel version:

=SUM(--TEXTBEFORE(TEXTAFTER(A2:A3,"$"),")"))

[mikepalerski]

Thanks Fluff13. I went with yours since it was the first on the list . It worked fine.

Thanks also dosydos and Glenn Kennedy

Much appreciated

[Glenn Kennedy]

You said "I went with yours since it was the first on the list". as good a reason as any!!

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[Fluff13]

Glad to help & thanks for the feedback.

[mikepalerski]

Sure thing!

[mikepalerski]

Could you please show me how to mark this as "solved"? I just searched the page and none of the [CTRL]F hits were fruitful.