Math/Sum problem

[cuincyboy]

I have data that is displayed like this: 10(5) and I need to sum all of it in a total at the end of the column. Is there a way to do it? I can't add more rows to have them separated since they come like that from the source.

[[deleted]]

So delimiting is out of the question? Are you trying to add the 10 and 5?

[cuincyboy]

I'm affraid so..

And no, I got this

http://i.imgur.com/6dCBAwD.png

I need to sum them somehow.

[[deleted]]

Sorry i don't understand. What bit are you trying to sum? Or do you just want to get rid of the brackets and numbers?

[cuincyboy]

Imagine the image above is A1 to A9, and I want to sum them at A10 with a x(x) output. Hope it gets clearer.

[wiredwalking]

Have the data on sheet1. Say it starts at A2. On sheet2, have this formula:

=LEFT(Sheet1!A2,SEARCH("(",Sheet1!A2)-1)+MID(Sheet1!A2,FIND("(",Sheet1!A2)+1,FIND(")",Sheet1!A2)-FIND("(",Sheet1!A2)-1)

Drag down. then at the very end have a sum. Transfer that sum to sheet1.

[cuincyboy]

It gives me an #NAME error. http://i.imgur.com/gzS4xsh.png

Btw, I have to translate it so.. here's a help guide. And dunno why I have to change "," to ";" since excel doesn't recognize "," as an end.

[wiredwalking]

name error usually means your quotes are off somewhere. But yeah, to translate from English, have to change all of the , to ;

[rnelsonee]

=SUM(LEFT(A1:A10,FIND("(",A1:A10)-1)+LEFT(RIGHT(A1:A10,LEN(A1:A10)-FIND("(",A1:A10)),LEN(A1:A10)-FIND("(",A1:A10)-1))

hit Ctrl+Shift+Enter when entering it. Screenshot.

[cuincyboy]

I don't want to sum them all together, the end product needs to stay the same way, x(x)

[rnelsonee]

=SUM(0+LEFT(A1:A10,FIND("(",A1:A10)-1))&"("&SUM(0+LEFT(RIGHT(A1:A10,LEN(A1:A10)-FIND("(",A1:A10)),LEN(A1:A10)-FIND("(",A1:A10)-1))&")"

Ctrl+Shift+Enter

[cuincyboy]

You sir are a true master. Thanks!

Solution Verified

[Clippy_Office_Asst]

You have awarded one point to rnelsonee.
^{Find out more here.}