Evaluate

[mgirvin]

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?

 

[Aladin Akyurek]

Try...

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

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

 

[MickG]

Hi, here's a UDF

Function ev(st As String) As Long
ev = Evaluate(Replace(st, "-", "+"))
End Function

Regards Mick

 

[Colin Legg]

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

=EVALUATE(SUBSTITUTE(Sheet1!$B$14,"-","+"))

Then it can be called from the worksheet with

=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.

 

[UniMord]

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.

 

[UniMord]

...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.

 

[mgirvin]

Dear Aladin,

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.