turning text into a calculated result:

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

On Apr 14, 3:43*pm, Wayne C Wayne
wrote:
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?

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(A22) 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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Wayne C" Wayne wrote in message
...
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?

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

--
Jacob (MVP - Excel)


"Wayne C" wrote:

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?