Sum of each cell having text string required in separate cell.

[SyedTabassumAli]

I need sum of each cell
I have tried multiple options but not able to
Actually there are different text symbols like "+", "(", ")", and space
I am stuck using Search / Find Function locating only one text symbol

Cell values are like
B3: 54 + 216 + 20
B4: 5 + 148 + 87 + 5
B5: 48 + (4+3) + 349 + 4

Result required is
C3: 290
C4: 245
C5: 166

[Paul]

There is a VBA function called EVALUATE that will, well, evaluate math strings like the examples you provided. Try this:

Open the VB Editor (ALT+F11)
Click Insert > Module
In the new module, paste the following code:

Please Login or Register  to view this content.

Close the VB Editor window
In cell C3, put the formula:

Please Login or Register  to view this content.

It should calculate the correct value. I tested on your other examples in your test workbook, and it worked each time.

Good luck!

[dosydos]

The only way i know how is to use name manager.
Select cell D3.
Then select NAME MANAGER from formula ribbon
Click New...
Name: Evaluate

Refers TO: =EVALUATE(SUBSTITUTE(Sheet1!B3," ",))

Hit ok.
in D3 type =evaluate and hit enter. Drag formula down

[SyedTabassumAli]

There is a VBA function called EVALUATE that will, well, evaluate math strings like the examples you provided. Try this:

Open the VB Editor (ALT+F11)
Click Insert > Module
In the new module, paste the following code:

Please Login or Register  to view this content.

Close the VB Editor window
In cell C3, put the formula:

Please Login or Register  to view this content.

It should calculate the correct value. I tested on your other examples in your test workbook, and it worked each time.

Good luck!

Thanks but I am not good with VBA, Is it possible to use functions or multiple functions in formula to resolve this?

[SyedTabassumAli]

The only way i know how is to use name manager.
Select cell D3.
Then select NAME MANAGER from formula ribbon
Click New...
Name: Evaluate

Refers TO: =EVALUATE(SUBSTITUTE(Sheet1!B3," ",))

Hit ok.
in D3 type =evaluate and hit enter. Drag formula down

Yes this perfectly works, but the problem here is I can't move the sum value to another column, for that I have to use Name Manager each time I am moving Total to a different column, that is why I am looking for a formula, that is easy to copy and paste to different cells.

[Paul]

You don't have to be good with VBA, you just have to follow the instructions I gave you ONE TIME. Save the file as an Excel Macro-enabled file (.xlsm) and that's it. Use the new =sum_text(cell) function anywhere within that workbook.

If you need the custom function in another workbook, open that one > insert a module > paste the code. You just need to save the files containing the function as .xlsm so it retains the code.

Performing math on text is not a natural feature in most programs, so you'll have to weight the pros and cons of spending an extra few seconds on some tasks.

[Paul]

And as for dosydos's solution, the Name Manager function you create can (and should be) relative to the current cell you're in.

https://superuser.com/questions/2533...were-a-formula (See answer from Laurentiu Mirica on Jun 27 '14 at 13:08)

Let's assume your string is in B3, and you're currently active cell is C3. When you go into Name Manager and create the new name (e.g. eval) and set the 'Refers to:' field to =EVALUATE(B3), you can then use the function

=eval

anywhere in the spreadsheet and it will reference the cell immediately to the left of that cell. (If you use it in column A you'll get an error, of course.)

[SyedTabassumAli]

Issue - Evaluate.JPG

This is an issue when using Evaluation with Name Manager

[Paul]

Save the file as a macro-enabled workbook (.xlsm), the same as if you used my code.

I believe in older versions of Excel there was a true Evaluate() workbook function, but it's now relegated to VBA. Even the Name Manager version apparently requires VBA references.