Converting calculation expressed as text into a formula
 

I would like to be able to convert a calculation expressed as text in one
column into the answer in the next column.

As a simple example, I would like to type "2 * 3" in column A and for "6" to
then appear in column B.

I will probably also need to do more complicated calculations such as "(6 +
7) * (3 + 3) as well.

I tried to create a macro to achieve this, but the problem is that it was
always specific to the row in which I was typing at the time the macro was
created.

Can anyone suggest a method?

As a bonus question, rather than the "*" I would like to be able to type "x"
in the text cell.

I managed to achieve this in a macro by copying the text to column C,
finding the "x"s and replacing them with "*"s within that and column D
(because you can't find and replace within a single cell) but then ran into
the problem above.

Thanks in advance.

Converting calculation expressed as text into a formula
 

Paul,

Not that it's any of my business but why? Excel has a programmed method of
inputting formula that will then calculate. For example you want to type:-

"2 * 3"

Excel would like:-

=2*3 and produce an answer which I think is less keystrokes.

Mike

"Paul Stagg" wrote:

I would like to be able to convert a calculation expressed as text in one
column into the answer in the next column.

As a simple example, I would like to type "2 * 3" in column A and for "6" to
then appear in column B.

I will probably also need to do more complicated calculations such as "(6 +
7) * (3 + 3) as well.

I tried to create a macro to achieve this, but the problem is that it was
always specific to the row in which I was typing at the time the macro was
created.

Can anyone suggest a method?

As a bonus question, rather than the "*" I would like to be able to type "x"
in the text cell.

I managed to achieve this in a macro by copying the text to column C,
finding the "x"s and replacing them with "*"s within that and column D
(because you can't find and replace within a single cell) but then ran into
the problem above.

Thanks in advance.

Converting calculation expressed as text into a formula
 

First enter this User Defined Function:

Function WhatIs(r As Range) As Variant
WhatIs = Evaluate(r.Value)
End Function

Then in B1 enter:

=WhatIs(A1)


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx


--
Gary''s Student - gsnu200826


"Paul Stagg" wrote:

I would like to be able to convert a calculation expressed as text in one
column into the answer in the next column.

As a simple example, I would like to type "2 * 3" in column A and for "6" to
then appear in column B.

I will probably also need to do more complicated calculations such as "(6 +
7) * (3 + 3) as well.

I tried to create a macro to achieve this, but the problem is that it was
always specific to the row in which I was typing at the time the macro was
created.

Can anyone suggest a method?

As a bonus question, rather than the "*" I would like to be able to type "x"
in the text cell.

I managed to achieve this in a macro by copying the text to column C,
finding the "x"s and replacing them with "*"s within that and column D
(because you can't find and replace within a single cell) but then ran into
the problem above.

Thanks in advance.

Converting calculation expressed as text into a formula
 

for RowCount = 1 to 100
.Range("B" & RowCount) = Evaluate(.Range("A" & RowCount))
next RowCount

"Paul Stagg" wrote:

I would like to be able to convert a calculation expressed as text in one
column into the answer in the next column.

As a simple example, I would like to type "2 * 3" in column A and for "6" to
then appear in column B.

I will probably also need to do more complicated calculations such as "(6 +
7) * (3 + 3) as well.

I tried to create a macro to achieve this, but the problem is that it was
always specific to the row in which I was typing at the time the macro was
created.

Can anyone suggest a method?

As a bonus question, rather than the "*" I would like to be able to type "x"
in the text cell.

I managed to achieve this in a macro by copying the text to column C,
finding the "x"s and replacing them with "*"s within that and column D
(because you can't find and replace within a single cell) but then ran into
the problem above.

Thanks in advance.