Convert cell's text to formula

[dsl224]

Is there a quick way, other than VBA, to put the contents of a cell that has the text of a formula into a second cell, but as a real formula?

What I mean is, if A1 has as text: abs(-1), I would like cell B1 to read the contents of A1 and evaluate it and put the results into B2.

TIA,
David

 

[Aladin Akyurek]

=EVAL(A1)

 

[dsl224]

I am getting #NAME?
Could it be version related?

Thanks again,
David

 

[Yogi Anand]

Hi dsl224:

EVAL in Aladin's post is a function available in MoreFunc Add-in that is available for free.

So, to be able to use EVAL, you need to have the MoreFunc Add-in installed -- otherwise you need to write another UserDefined function in place of EVAL.

 

[dsl224]

Thanks. I don't want to start using Add-ins. Do you know of any other solution or UDF?

 

[Yogi Anand]

Thanks. I don't want to start using Add-ins. Do you know of any other solution or UDF?

Hi dsl224:

You can use the following UDF ...

Function yEval(entry As String)
    yEval = Evaluate(entry)
End Function

and its usage is illustrated in the following ...

y030917h1.xls
	A	B	C	D	E	F
1	abs(-1)		1		a	3
2	5*4		20		b	5
3	vlookup("b",$E$1:$F$4,2,0)		5		c	2
4					d	1
Sheet4

I hope this helps. If I have misunderstood your question -- my apologies!

 

[dsl224]

Thank you very much.

David

 

[TVanStone]

Does anybody have a VB solution for doing the opposite?

Daily, I must format the flatfile result of a days worth of web form inquiries. Names, addresses and cities are WRITTEN LIKE THIS or tHiS, etc.

=proper(D2) returns the proper result. Using Notepad and a series of Copies/Pastes I can copy the resulting values over the original data.

Is there a way to capture the results of the formula so as to copy it over the original, incorrectly cased data?

 

[klb]

How about:

Sub ProperCase()
Dim c As Range

For Each c In Selection
c.Value = Application.WorksheetFunction.Proper(c.Value)
Next c
End Sub

 

[TVanStone]

^ brilliant - thanks!