turning text into a calculated result:

  • Thread starter Thread starter Wayne C
  • Start date Start date
W

Wayne C

Hello all

I have a spreat sheet that looks something like this:

Cases Total cigarettes
13*10*20 2,600
13*10 130
12*10*20 2,400

with col A as "Cases" & column b "total ciggies"

basically I want a formula to calculate the sum in col A to display the
result in col B

without having to manually into the calculation each time & without using
"Text to Columns" if possible

any help please?
 
You could set up this user-defined function:

Function Eval(Rng As Range) As Variant
Application.Volatile True
Eval = Evaluate(Rng.Text)
End Function

and then you can use this in B2:

=Eval(A2)

and copy down.

Hope this helps.

Pete
 
While Pete has given an excellent VBA solution, you may not be familiar with
VBA and need a 'quick fix'
Here is a one way
I will assume the data is in A2:A100; that the most you have is 4 numbers
separated by asterisk, and there is nothing in columns B thru E
Select A2:A100;
Use Data | Text to Columns with Delimited option and in the Other box enter
a *
Now you numbers will be in 2,3 or 4 columns (A to D)
In E2 enter =PRODUCT(A2:D2) and copy down the column - the fast way it to
double click the fill handle (small solid square in lower right corner) of
the active cell.
PRODUCT wins here over =A2*B2*C2*D2 in that it PRODUCT ignores empty cells
while the formula treats them as zero
best wishes
 
Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

=getcalculation(A2)

Function GetCalculation(strTemp As String) As Variant
GetCalculation = Application.Evaluate("=" & strTemp)
End Function
 
Back
Top