Convert text into formula.

  • Thread starter Thread starter lukus2005
  • Start date Start date
L

lukus2005

Is there a way, without resorting to a macro, to convert a text string
into a formula?

Example: A1 contains the text string "'B1+C1"

In A2, I would like a formula that converts the string in A1 into a
formula. Does such a function exist?

Ultimately, what I want to achieve is to concatenate two text string,
from two cells, to create a longer formula.
 
This UDF can do simple things as your example. Not sure what your "real"
text strings consist of.

Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function

=evalcell(A1) entered in A2

Copy the UDF to a general module in your workbook.


Gord Dibben MS Excel MVP
 
hi lukus,

You could use something like:
=INDIRECT(LEFT(A1,FIND("+",A1)-1))+INDIRECT(RIGHT(A1,FIND("+",A1)-1))
but it seems a bit pointless. In the real world it'd probably be better to simply insert the '=' before the expression, otherwise,
you need to code for all possible formula combinations and you'll probably run well over the formula length limit long before you've
come anywhere near exhausting the possibilities.

The alternative is to use a UDF, per Gordon's suggestion, but that still involves vba (macro) code.
 
I tried your example Gord, but it doesn't work in the way I had hoped.
That's not due to your function but rather due to an Excel limitation.

Here's a more precise look at what I was trying to achieve...

A1 thru A10 contain a validation list of 3 duct types
B1 thru B10 contains Length dimensions
C1 thru C10 contains Width dimensions
D1 thru D10 contains the total area using one of the formulas below,
depending on the duct type selected in A1.

N20 contains the formula for Type 1 duct: =((INDIRECT("B"&ROW()))*
(INDIRECT("C"&ROW()))+8)/144
N21 contains the formula for Type 2 duct: =((INDIRECT("B"&ROW()))*
(INDIRECT("C"&ROW()))+8-12)/3.16
N22 contains the formula for Type 3 duct: =((INDIRECT("B"&ROW()))*
(INDIRECT("C"&ROW()))*(INDIRECT("D"&ROW()))

What I was hoping to do was use an IF statement in D1 thru D10 that
would look similar to this...

=IF((INDIRECT("A"&ROW())="Type 1",evalcell(N20),IF((INDIRECT("A"&ROW())
="Type 2",evalcell(N21),IF((INDIRECT("A"&ROW())="Type 3",evalcell
(N22),""))

I have had other solutions given to me but due to my lack of macro
knowledge, I couldn't figured out how to modify them to work in my
spreadsheet.

The problem appears to be passing the ROW number thru the evalcell UDF
which doesn't appear to happen.
 
In case Gord is busy,
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
I tried your example Gord, but it doesn't work in the way I had hoped.
That's not due to your function but rather due to an Excel limitation.

Here's a more precise look at what I was trying to achieve...

A1 thru A10 contain a validation list of 3 duct types
B1 thru B10 contains Length dimensions
C1 thru C10 contains Width dimensions
D1 thru D10 contains the total area using one of the formulas below,
depending on the duct type selected in A1.

N20 contains the formula for Type 1 duct: =((INDIRECT("B"&ROW()))*
(INDIRECT("C"&ROW()))+8)/144
N21 contains the formula for Type 2 duct: =((INDIRECT("B"&ROW()))*
(INDIRECT("C"&ROW()))+8-12)/3.16
N22 contains the formula for Type 3 duct: =((INDIRECT("B"&ROW()))*
(INDIRECT("C"&ROW()))*(INDIRECT("D"&ROW()))

What I was hoping to do was use an IF statement in D1 thru D10 that
would look similar to this...

=IF((INDIRECT("A"&ROW())="Type 1",evalcell(N20),IF((INDIRECT("A"&ROW())
="Type 2",evalcell(N21),IF((INDIRECT("A"&ROW())="Type 3",evalcell
(N22),""))

I have had other solutions given to me but due to my lack of macro
knowledge, I couldn't figured out how to modify them to work in my
spreadsheet.

The problem appears to be passing the ROW number thru the evalcell UDF
which doesn't appear to happen.
 
Is there a way, without resorting to a macro, to convert a text string
into a formula?

Example:  A1 contains the text string "'B1+C1"

In A2, I would like a formula that converts the string in A1 into a
formula.  Does such a function exist?

Ultimately, what I want to achieve is to concatenate two text string,
from two cells, to create a longer formula.
Click to expand...

Try this:

Sub macro2()
Range("A1") = 5
Range("B1") = 10
Range("A4").Select
Selection.Cells(1, 1) = "A1 + B1"
Selection.Cells(2, 1) = "=" & Selection.Cells(1, 1)
End Sub
Jim
 
Try this:

Sub macro2()
   Range("A1") = 5
   Range("B1") = 10
   Range("A4").Select
   Selection.Cells(1, 1) = "A1 + B1"
   Selection.Cells(2, 1) = "=" & Selection.Cells(1, 1)
End Sub
Jim
Click to expand...

Jim, I'm not quite sure what it is your macro does. Do I use it in
combination with Gord's UDF? I have no clue what this does.
 
Jim, I'm not quite sure what it is your macro does.  Do I use it in
combination with Gord's UDF?  I have no clue what this does.
Click to expand...

The contents of A5 (Selection.Cells(2, 1)) is the evaluation of the
text-string formula stored in A4 that uses data in cells A1 and B1.
See below for a step by step . The last statement before "end sub"
could be:
Range("A5") = "=" & Range("A4"). The lines above just put test data in
appropriate cells..

I hope this helps. My example was pretty closely related to yours.

Jim

Sub macro2()
Range("A1") =
5
Put a value in cell A1
Range("B1") =
10
Put a value in cell B1
Range
("A4").Select
Target cell for the formula text string = A4
Selection.Cells(1, 1) = "A1 +
B1" Place formula in target
cell
Selection.Cells(2, 1) = "=" & Selection.Cells(1, 1)
Evaluate the formula in cell A5
End Sub
 
Back
Top