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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"liu" wrote in message
...
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,

On Feb 12, 9:16*am, "Don Guillett" wrote:
Are you saying
=a2*b2
or
=a2*b2*c2

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.

"liu" wrote in message
...
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,

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


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"liu" wrote:

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,

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)


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"liu" wrote:

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,

On Feb 12, 11:39*am, Shane Devenshire
wrote:
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)

--
If this helps, please click the Yes button

Amazing! Yes, it works.

Thank you all for the help! I learned a lot.