Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
turning text into a calculated result:
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
turning text into a calculated result:
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
turning text into a calculated result:
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula, not the result is displayed in calculated cells - why? | Excel Worksheet Functions | |||
Pivot Table: How do I hide calculated items that result in zero? | Charts and Charting in Excel | |||
PivotTable:Using a calculated field result in another calculated f | Excel Worksheet Functions | |||
Shifting Calculated Result - Re-Issued | Excel Worksheet Functions | |||
Shifting Calculated Result | Excel Worksheet Functions |