Get result from formula in text string?

[bebo021999]

I have text string in A1 like this:
Turn Over: = 2.1*3.5*4.2
or like this:
Turn Over: = 2.1*3.5*4.2 XYZ

(May include +,-,/ in it)
Which formula in B1 to get result: =2.1*3.5*4.2 = 30.87 ?

Thanks in advance for your assist.

 

[anandvarma]

try this

Excel Workbook
	A	B	C
1	data	Existing formula	New formula
2	2.1	Turnover:= 2.1*3.5*4.2	Turnover:= 2.1*3.5*4.2= 30.87
3	3.5
4	4.2
Sheet1

 

[bebo021999]

try this

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 34px"><COL style="WIDTH: 144px"><COL style="WIDTH: 191px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>data</TD><TD>Existing formula</TD><TD>New formula</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">2.1</TD><TD>Turnover:= 2.1*3.5*4.2</TD><TD>Turnover:= 2.1*3.5*4.2= 30.87</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">3.5</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">4.2</TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>="Turnover:= "&A2&"*"&A3&"*"&A4</TD></TR><TR><TD>C2</TD><TD>="Turnover:= "&A2&"*"&A3&"*"&A4&"= "&(A2*A3*A4)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Thanks for your quick reply, but my explanation may not clear so far!
In your table, I mean B2 is text string decribe how to calculate turn over;
I expect: C2 = 30.87
Next row:
B3 = "some text string...:=3*3*4 XYZ"
C3 = 36
...
Mean C will do calculate following instruction in B

Thanks in advance,

 

[anandvarma]

SOME THING LIKE THIS

Excel Workbook
	A	B	C
1	data
2	2.1	Turnover:= 2.1*3.5*4.2	30.87 XYZ
3	3.5
4	4.2
5	XYZ
Sheet1

 

[CWatts]

If I am understanding correctly, he has cells that are text strings. Those text strings contain arithmetic expressions.

There are no cells to input the variables, rather they need to be extrapolated from the expression and then display the expression as well as the result in an adjacent cell.

For example:

Sheet1

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:151px;"><col style="width:125px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td> Turnover:= 2.1*3.5*4.2</td><td>2.1*3.5*4.2 = 30.87</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>B1</td><td>="2.1*3.5*4.2 = "&2.1*3.5*4.2</td></tr></tbody></table></td></tr></tbody></table>


Can you confirm this is what you're looking for?

 

[Blade Hunter]

I believe the OP is looking for the formula to be derived from the text.

Kind of like the indirect function but without cell refs.

This will not work but may explain what i believe the OP to be after:

=INDIRECT((SUBSTITUTE(A1,"Turnover:","")))

So they want a formula that will calculate the formula which is written in that part of the cell.

 

[anandvarma]

if you are looking for suggestion by CWATT the formula will be something like this

Excel Workbook
	B	C
2	Turnover:= 2.1*3.5*4.2	30.87
Sheet1

but you need to downloan MOREFUNC addin

 

[anandvarma]

I believe the OP is looking for the formula to be derived from the text.

Kind of like the indirect function but without cell refs.

This will not work but may explain what i believe the OP to be after:

=INDIRECT((SUBSTITUTE(A1,"Turnover:","")))

So they want a formula that will calculate the formula which is written in that part of the cell.

geting #ref! error

 

[bebo021999]

I am thinking of solution which use Macro 4:
C1=EVAL(B1)
with name EVAL = EVALUATE(TEXT)
but it returns #ERROR
Anyway, I refer to formula which I can understand easily!

 

[Marcelo Branco]

Hi,

Has the operation always 3 numbers?
N1*N2*N3
N1+N2/N3
N1-N2*N3
.....

Or it can be?
N1*N2
N1/N2+N3*N4
....

M.