Evaluate

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Mar 4, 2009

• #2

Try...

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

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

 

Upvote 0

M

MickG

MrExcel MVP

Joined

Jan 9, 2008

Messages

14,841

• Mar 4, 2009

• #3

Hi, here's a UDF

Code:

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

Regards Mick

 

Upvote 0

Colin Legg

MrExcel MVP

Joined

Feb 28, 2008

Messages

3,503

Office Version

1. 365

Platform

1. Windows

• Mar 4, 2009

• #4

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

Click to expand...

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.

 

Upvote 0

UniMord

Active Member

Joined

May 6, 2002

Messages

311

• Mar 4, 2009

• #5

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: Mar 4, 2009

Upvote 0

UniMord

Active Member

Joined

May 6, 2002

Messages

311

• Mar 4, 2009

• #6

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

Click to expand...

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

 

Upvote 0

mgirvin

Well-known Member

Joined

Dec 15, 2005

Messages

1,236

Office Version

1. 365

Platform

1. Windows

• Mar 4, 2009

• #7

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.

 

Upvote 0