Extracting numbers from a text and multiplying them numbers eachother

[cadandcode]

Hello, I checked most of the topics but couldnt find the exact formula.

	A	B
1	4 M 10 AD	40
2	20 M 7 AD	140

I would like to get B2 results just like that from A1 and A2 columns. Extract 4 and 10 from A1 and multiply them eachother in B1 (B1 is just an example) like I tried to describe above.
I found a vba code to extract 4 and 10 from that text cell into B1 like "410" but I couldnt find a way to multiply them as "=4*10" in B1 instead of putting them together like "410".

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B
1
2	4 M 10 AD	40
3	20 M 7 AD	140
Master

Cell Formulas
Range	Formula
B2:B3	B2	=PRODUCT(FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m"))

 

[cadandcode]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B
1
2	4 M 10 AD	40
3	20 M 7 AD	140
Master

Cell Formulas
Range	Formula
B2:B3	B2	=PRODUCT(FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m"))

Thank you for your kind welcome and quick help sir, it gives that error when I type formula in B2 cell.

 

[awoohaw]

as long as you always have the M and AD the same this is an option:

WorkBook1.xlsx
	A	B
1
2
3	4 M 10 AD	40
4	20 M 7 AD	140
5
Sheet4

Cell Formulas
Range	Formula
B3:B4	B3	=LEFT(A3,FIND(" M",A3)-1) * LEFT(SUBSTITUTE(A3,LEFT(A3,FIND("M ",A3)+1),""),FIND(" A",SUBSTITUTE(A3,LEFT(A3,FIND("M ",A3)+1),""))-1)

 

[Fluff]

it gives that error when I type formula in B2 cell.

Do you normally use commas as the separator in a formula, or do you use semi-colons?

 

[cadandcode]

as long as you always have the M and AD the same this is an option:

WorkBook1.xlsx
	A	B
1
2
3	4 M 10 AD	40
4	20 M 7 AD	140
5
Sheet4

Cell Formulas
Range	Formula
B3:B4	B3	=LEFT(A3,FIND(" M",A3)-1) * LEFT(SUBSTITUTE(A3,LEFT(A3,FIND("M ",A3)+1),""),FIND(" A",SUBSTITUTE(A3,LEFT(A3,FIND("M ",A3)+1),""))-1)

I changed all "," to ";" and it worked.

Do you normally use commas as the separator in a formula, or do you use semi-colons?

I think it is ";" because I changed @awoohaw 's all "," symbols to ";" and it worked.
Why did that difference occure?

 

[Fluff]

Some countries use the ; as a separator & some use , You just need to use your normal separator.

 

[cadandcode]

Some countries use the ; as a separator & some use , You just need to use your normal separator.

Thank you and how can I multiply them numbers from A1, A2, A3.... A99... and sum all in B1? Like B1=4*10+20*7+......

 

[Fluff]

With your version of excel, you probably better off putting the formula in col B, fill down & then add all the results up.

 

[cadandcode]

With your version of excel, you probably better off putting the formula in col B, fill down & then add all the results up.

I think it didnt work, one cell (B1) can get result of A1 (4*10) and I believe we can put there some (+) and add other cells, tried to make it A1:A4 to perform that extract and multiply process from cells A1 to A4 but it just gave A1 results.