Auto-calculate 2 column that contains numbers and words words

[RepetitiveLady]

Hi everyone,

I've been using conventional method to do this and it's time consuming. I would like to total up 2 column. A multiply B to be exact. Below are some examples:

Table 1 - Before totaling up:

<table dir="ltr" border="1" bordercolor="#05ad00" cellpadding="2" cellspacing="1" height="154" width="448"><tbody><tr><td align="center" height="4" width="14%">

Quantity
​

</td><td height="4" width="14%">Product
</td></tr><tr><td style="vertical-align: top;">5
</td><td style="vertical-align: top;">2 x Button A White
</td></tr><tr><td style="vertical-align: top;">3
</td><td style="vertical-align: top;">4 x Button B Pink
</td></tr><tr><td style="vertical-align: top;">4
</td><td style="vertical-align: top;">5 x Ribbon A Black
</td></tr><tr><td style="vertical-align: top;">2
</td><td style="vertical-align: top;">3 x Thread A White
</td></tr><tr><td style="vertical-align: top;">6
</td><td style="vertical-align: top;">2 x Cloth A Blue
</td></tr></tbody></table>

Table 2 - After totaling up:

<table dir="ltr" border="1" bordercolor="#05ad00" cellpadding="2" cellspacing="1" height="154" width="448"> <tbody><tr><td align="center" height="4" width="14%">

Quantity
​

</td><td height="4" width="14%">Product
</td></tr><tr> <td style="vertical-align: top;">10
</td> <td style="vertical-align: top;">Button A White
</td> </tr> <tr> <td style="vertical-align: top;">12
</td> <td style="vertical-align: top;">Button B Pink
</td> </tr> <tr> <td style="vertical-align: top;">20
</td> <td style="vertical-align: top;">Ribbon A Black
</td> </tr> <tr> <td style="vertical-align: top;">6
</td> <td style="vertical-align: top;">Thread A White
</td> </tr> <tr> <td style="vertical-align: top;">12
</td> <td style="vertical-align: top;">Cloth A Blue
</td> </tr> </tbody> </table>

I need to have the sum of the "Quantity" multiply "Product". Or in short A x B.
And the end result need to have the number and "x" sign removed while keeping on the the products names. (2 x ) Take note it's "number" space "symbol" space.

Can anyone help me out? I'm tired of the conventional method which is extremely time consuming as the list is getting really long.

Cheers
- Cassandra -

 

[Phox]

What do you wish those labels to be?

 

[RepetitiveLady]

Hi,

Table 1 - Before processing

<table dir="ltr" border="1" bordercolor="#05ad00" cellpadding="2" cellspacing="1" height="154" width="448"><tbody><tr><td align="center" height="4" width="14%">

Quantity
​

</td><td height="4" width="14%">Product
</td></tr><tr><td style="vertical-align: top;">5
</td><td style="vertical-align: top;">2 x Button A White
</td></tr><tr><td style="vertical-align: top;">3
</td><td style="vertical-align: top;">Button B Pink
</td></tr><tr><td style="vertical-align: top;">4
</td><td style="vertical-align: top;">5 x Ribbon A Black
</td></tr><tr><td style="vertical-align: top;">3
</td><td style="vertical-align: top;">6.5 feet Thread A White
</td></tr><tr><td style="vertical-align: top;">6
</td><td style="vertical-align: top;">9925 Cloth A Blue</td></tr></tbody></table>
For example, "9925 Cloth A Blue", at the end of processing the data, i want it to be the same "9925 Cloth A Blue".
Same goes for the "6.5 feet Thread A White".
Like the output below:

Table 2 - After processing

<table dir="ltr" border="1" bordercolor="#05ad00" cellpadding="2" cellspacing="1" height="154" width="448"><tbody><tr><td align="center" height="4" width="14%">

Quantity
​

</td><td height="4" width="14%">Product
</td></tr><tr><td style="vertical-align: top;">10
</td><td style="vertical-align: top;">Button A White
</td></tr><tr><td style="vertical-align: top;">3
</td><td style="vertical-align: top;">Button B Pink
</td></tr><tr><td style="vertical-align: top;">20
</td><td style="vertical-align: top;">Ribbon A Black
</td></tr><tr><td style="vertical-align: top;">3
</td><td style="vertical-align: top;">6.5 feet Thread A White
</td></tr><tr><td style="vertical-align: top;">6
</td><td style="vertical-align: top;">9925 Cloth A Blue</td></tr></tbody></table>
You can see the it keeps the whole product name (words and numbers). Only extracting the "2 x " or "3 x " and so on.

- Cassandra -

 

[sanrv1f]

try this

Excel Workbook
	A	B	C	D	E	F
1	Quantity	Product			Quantity	Product
2	5	2xButtonAWhite			10	ButtonAWhite
3	3	ButtonBPink			3	ButtonBPink
4	4	5xRibbonABlack			20	RibbonABlack
5	3	6.5feetThreadAWhite			3	6.5feetThreadAWhite
6	6	9925ClothABlue			6	9925ClothABlue
Sheet2

Cell Formulas
Range	Formula
E2	=IF(AND(ISNUMBER(SEARCH("x",B2)),ISNUMBER(VALUE(LEFT(B2,SEARCH("x",B2)-1)))),VALUE(LEFT(B2,SEARCH("x",B2)-1)),1)*A2
F2	=IF(AND(ISNUMBER(SEARCH("x",B2)),ISNUMBER(VALUE(LEFT(B2,SEARCH("x",B2)-1)))),RIGHT(B2,LEN(B2)-SEARCH("x",B2)-1),B2)

 

[RepetitiveLady]

Thank you so much sanrv1f,

The codes works like magic. It totally process everything keeping all the product name only removing the "2 x", "3 x "...

Work is easier now. Thanks to all the Excel Guru here. Really appreciate the help. So happy now.

Cheers
- Cassandra -