Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use of text and calculation in a formula | Excel Discussion (Misc queries) | |||
converting results from formula to text in a new column | New Users to Excel | |||
Converting text string back into a formula | Excel Discussion (Misc queries) | |||
what formula converting number to text in english or hindi in exl | Excel Worksheet Functions | |||
Sum formula result expressed as a date | Excel Discussion (Misc queries) |