Calculate/sum multiple values in each cell in a range then replace with the total of every

Dear All, thank you for your continuous help. I have a small request and I need your help, I have a table, a range of cells that contain multiple values separated by + (and the separation sign could be changed if needed), for example I have cells that look like:

3+1 2+8
9+5
5+6 9+1 6+8
3+1 2+8
9+5 5+6 9+1 6+8
5+6 9+1 6+8
..... etc

Please note that there could be thousands of such cells inside the sheet.

And I need to replace every cell with the total sum of its multiple values inside it, so based on the above example, the results should be as following inside each related cell:

4 10
14
11 10 14
4 10
............... etc

Thanks a lot for your help in advance
Charlie

Hi Charlie,

My code abilities seems to be working today. I've created some code that will do what you want. You simply need to run the macro while looking at any sheet. If the cell has a plus sign in it then it will add the parts.
See the example and here is the code.

Please Login or Register  to view this content.

OK Charlie,

Here is your sample back after running my code on it.

Hi Marvin,

I am just wondering, in case we need to change just the type of mathematical operation, for example to run subtraction instead of addition, or multiplication or division, is it possible? I mean for example to replace 3-1 in the same cell with 2 , or replace 3*2 with 6 in the same cell , or replace 6/3 with 2 in the same cell ... etc, and of course apply on all cells that meet the condition in the whole sheet or preferably a selected range.

I think there could be 2 scenarios:
either we have a separate macro for each type of operation assuming that the whole sheet is of one type (e.g. only addition or only .. etc)
or we have one macro that checks if the sign inside the cell is + or - or * or / then processes it accordingly, and this solution would be better.

Many thanks
Charlie

OK Charlie,
Only because you gave me rep do I solve this next and harder problem... It wasn't obvious but I think this might work for you. The code looks like:

Please Login or Register  to view this content.

See the attached for a sample...

Hey TM,

I see you used an EVAL function or something to give your answer. I don't find an EVAL function in my Excel 2010. I do find http://chandoo.org/wp/2011/05/16/lost-excel-functions/ that claims to know how to do "EVALUATE" but I'm missing something in using it.

Care to teach me how you solved this problem? What version of Excel are you using?

First of all, I do appreciate all your help

Hi Marvin,

Thank you for your help, but when I tried to run the macro ON DIFFERENT SHEET, it just added the = sign at the beginning of each non-empty cell , so the cells became like:
=9+1 =8/2
=2*3 =8-7 …etc
So I hope you can fix it or let me know what I did wrong.

Hi TM,

Thank you for your help, this EVAL solution looks great, but there are 2 problems with it:
1st : it displays the results in different range of cells, while the macro of Marvin, displayed the results inside each original cell, which is the solution I need.
2nd : It runs only on a pre-defined or pre-selected range of cells, (e.g. in the attached sample A1:F18), while I need the solution to run on the whole sheet. Actually I do not know how to use the EVAl function, I have only that interface as an object inside the sheet and it’s unclickable! , and there is no macro in the (Microsoft Visual basic for Application page!!), so I do not know where to click to run it, or where I can modify or change the selected range or …. I would appreciate your help to let me know how to use it.

Many thanks
Charlie