calculation based on mixed entries of another cell?

  • Thread starter Thread starter liu
  • Start date Start date
L

liu

I have one column with dimensions like this:
21.9" x 17.6" x 8.9"

I'd like to calculate the multiplication of the first 2 numbers. Right
now, I have to manually enter =21.9*17.6 in another cell.
I wonder if it's possible to write something that automatically picks
up 2.19 and 17.6 and multiplies the 2. The text in the column has the
same format.

Thanks in advance for the help,
 
Are you saying
=a2*b2
or
=a2*b2*c2
Click to expand...

Thanks for the help. I'm saying one cell with 21.9" x 17.6" x 8.9" and
I'd like to have another cell with function to calculate 21.9*17.6
automatically without manually entering the numbers. I have one column
with entries like that so it would take a long time to type the 2
numbers from the other cell.
Sorry for the confusion.
 
Yes, it's possible, but experience shows that every time to force Excel to
do something it wasn't originally designed to do, it leads only to
frustration.

You're almost certainly better off doing the following:
1. Convert your data to three columns of numbers (length, width, height).
This can easily be done with the Text to Columns feature.
2. Now arithmetic is easy to do -- for example, to multiply the first two
dimensions, just use =a2*b2
3. To recreate your original cell (for display or print purposes), just
concatenate the numbers, as in =a2&" x "&b2&" x "&c2

Regards,
Fred.
 
Hi,

If your data is consistant you might get away with this idea:
1. Highlight the column of data and choose Data, Text to Columns, choose
Delimited
2. Click Next and check Space and in the box beside Other enter x and click
Next
3. Select the 3rd column in the Data preview pane and select Do not import
column (skip).
4. Change the destination cell to a blank column, same row as the first
entry and click Finish
5. Select the two column of output and Press Ctrl+H enter " in the Find what
box and nothing in the Replace with box and click Replace all.
Now you can use a simple =B1*C1 formula
 
Hi,

and here is a formula solution

=LEFT(A1,FIND("""",A1)-1)*MID(A1,FIND("x",A1)+1,FIND("""",A1,FIND("""",A1)+1)-FIND("x",A1)-1)
 
Back
Top