Multiplying numbers in a single cell

G

gacollege

I have 12 x 6 x 20 (as a length x highth x width) in cell A1 and I woul
like B2 to represent the total area. In other words multiply 12 x 6
20 to equal 1440
 
D

Dave Peterson

I agree with Frank that it would be better to separate each value into its own
cell.

But if you want you could use a userdefined function:

Option Explicit
Function Eval(myStr As String) As Variant
myStr = Application.Substitute(LCase(myStr), "x", "*")
Eval = Application.Evaluate(myStr)
End Function

Then use another cell with:
=eval(a1)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=eval(a1)

This function is pretty specific. You may want to start using standard
arithmetic operators in your strings to make it somewhat more useful:

12 * 6 * 20
 
B

BenjieLop

It is probably easier and the formula will be less cumbersome if you
separate your dimensions into 3 cells. IMO only.

For example, the length will be entered in Cell A1; the width in Cell
B1 and the height in Cell C1. And then on Cell D1, enter the formula

=A1*B1*C1

Also, when you multiply all your dimensions (as you indicated what you
wanted to do), you are calculating the VOLUME and NOT the area.

I have 12 x 6 x 20 (as a length x highth x width) in cell A1 and I would
like B2 to represent the total area. In other words multiply 12 x 6 x
20 to equal 1440.
Click to expand...
 
Top