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".

 

[awoohaw]

@Fluff @awoohaw this solved it. No matter there is blanks between each or different kind of characters, that vba code directly takes and multiplies every single numbers into the cell where macro targeted. Great work guys, thank you for all your efforts so far!

thanks @Fluff.

 

[Fluff]

That code does exactly what the formula in post#2 does.

 

[cadandcode]

That code does exactly what the formula in post#2 does.

Umm, I maybe ruined the formula while trying to change semicolons :/

 

[Fluff]

But you said it works.
As long as you have something that works that's fine.

 

[cadandcode]

But you said it works.
As long as you have something that works that's fine.

I tried it, now I remembered I have already tried it sir, it only takes first number.

 

[Fluff]

Are you trying that in 2021 or 2019?

 

[cadandcode]

Are you trying that in 2021 or 2019?

19

 

[Fluff]

In that case you will need to confirm the formula with Ctrl Shift Enter rather than just Enter.

 

[cadandcode]

In that case you will need to confirm the formula with Ctrl Shift Enter rather than just Enter.

That one worked as well, how great you are. Hugs from Turkey mate!

 

[cadandcode]

In that case you will need to confirm the formula with Ctrl Shift Enter rather than just Enter.

I added sumproduct in beginning of your formula to make 2 cells that calculated by your formula sum in 1 cell. What is wrong with that line?
=SUMPRODUCT(PRODUCT(FILTERXML("<k><m>"&SUBSTITUTE(A2:A3," ","</m><m>")&"</m></k>","//m")))