# Evaluate

#### mgirvin

##### Well-known Member
Dear Smartest Excelers In The World,

If I have this data in cell B14:

01-12-13-14-15

and I use this formula:

=SUBSTITUTE(B14,"-","+")

to generate:

01+12+13+14+15

How can I SUM this string so that in one cell I get the answer 55?

Any ideas?

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Try...

Morefunc's EVAL or EVAL in VBA (coded by FastExcel, available in a thread on this site)...

=EVAL(SUBSTITUTE(B14,"-","+"))

Hi, here's a UDF
Code:
``````Function ev(st As String) As Long
ev = Evaluate(Replace(st, "-", "+"))
End Function``````
Regards Mick

If I have this data in cell B14:

01-12-13-14-15

How can I SUM this string so that in one cell I get the answer 55

Here's another way, the general outline being:

Go to Insert | Name | Define

Add a name, for example MyFormula
Code:
``=EVALUATE(SUBSTITUTE(Sheet1!\$B\$14,"-","+"))``

Then it can be called from the worksheet with
Code:
``=MyFormula``

Evaluate is an old Excel 4.0 macro function. It can't be used directly in the worksheet so the approach isn't particularly flexible.

Actually, the XLM formula is slightly more flexible than you're making it out to be. Instead of using an absolute reference, use a relative reference and you can use it all over the place.

If you'll take the SUBSTITUTE part out of the formula, and enter it in a separate cell, you'll be able to use EVALUATE more generically. So, for example, if you'll enter in B15: =SUBSTITUTE(B14, "-", "+"), and then while B16 is selected, define Eval as =Sheet1!B15, then it'll always evaluate the cell above.

Last edited:
...define Eval as =Sheet1!B15, then it'll always evaluate the cell above.

Sorry, I meant to say: Define Eval as =EVALUATE(Sheet1!B15), then =Eval will always evaluate the cell above.

The EVAL works perfectly! Thank you.

Thanks for the other ideas also, MickG, Colin L, and UniMord!

Another idea for a named formula:

If I have this data in cell B14:

01-12-13-14-15

and I click in cell B15 and define the named formula:

=EVAL(SUBSTITUTE(!B14,"-","+"))

It will work on any sheet in any cell if the string is one cell above.

Replies
1
Views
88
Replies
3
Views
126
Replies
3
Views
118
Replies
3
Views
228
Replies
5
Views
90

1,203,643
Messages
6,056,520
Members
444,871
Latest member
Vishal Gupta

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back