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
--
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?