Convert String to Formula

[betsig250]

Hello.. I have a table and I would like to use another cell value as a formula. I have 8 massive MID statements will be used based on another value in the table. Instead of using a if then statement I would like to just reference a cell with that formula in it.

When I do that the table just shows =(formula). How can I get excel to calculate the string as a formula?

Thanks!

 

[Luke M]

You can do it, but have to be sneaky.
There's no direct function in XL that can take a string and turn it into a formula, but there is a hidden way. Select the cell where you want to put the formula. Then, go to Define Names and define "newForm" as:
=Evaluate(A2)
Hit ok.
Then, in your cell, type:
=newForm

Voila! the formula now calculates. The Evaluate function is a VB function, and for some reason (???) is only accessed in a workbook when in a named range.

Note: Be careful about absolute/relative references. If you were in cell B2 when you wrote the above formula as-is, and you then moved to cell D2 and typed
=newform
it would be looking at cell C2.

 

[betsig250]

Now say that the formula is in a table but I am going to use Vlookup to find the correct formula based on which value I want.
Table is the following.
Headers --Type, Formula
Value1, Mid()
Value2, Mid()
and so on til the 8th one. On the data table I will have the Value but then the next cell will be the vlookup to grab the correct formula. Then I need it to be evaluated.
Thanks!

 

[Luke M]

Can use a nested type structure, perhaps like:
=Evaluate(VLOOKUP(SomeCell,MyTable,2,False))

PS. I am curious about your layout and what you are actually trying to do...it might be better to use some CHOOSE or INDEX type functions, rather than try to read some MID strings stored in other cells.

 

[betsig250]

In essence I have a table of descriptions with 4 portions to it (Type, Description, Size, Color). There are 8 different types that each description could be. I can break out the type no problem out of the string. However the other 3 portions that I have to break out have no rhyme or reason to them (different spaces, naming, everything) therefore no text to columns, no standard mid statement. However, they did stay consistent with each type for the different portions. Therefore I have 8 formulas for each portion that I have to break out. So instead of writing an if statement that will be easily 500 characters long I was hoping to just write each formula once in a table and grab the corresponding formula for the Type.

 

[betsig250]

But the way it looks I might just have to write some VBA to get this done and use select statements for each portion.

 

[Luke M]

... So instead of writing an if statement that will be easily 500 characters long I was hoping to just write each formula once in a table and grab the corresponding formula for the Type.

This is where I think you can simplify. Rather than having multiple MID functions, you really need different numbers inside the MID. Think something like:
=MID(MyWord,SomeFormula,OtherFormula)
Where the two inside formula we'll need to know how you know which of the 8 to use. Is it based on size or something? Could do
Someformula:
LOOKUP(LEN(A2),{10,20,30,40},{3,4,5,6})

I doubt the above will exactly work, just wanted to give you alternate ideas about how to approach the problem.