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]

As I said, you are probably better off putting the formula into B1 to B4 & then add up the results.

 

[cadandcode]

As I said, you are probably better off putting the formula into B1 to B4 & then add up the results.

Alright, that is cool. Thank you for your effort sir.

 

[Fluff]

You could try

=LET(a,FILTERXML("<k><m>"&SUBSTITUTE(TEXTJOIN(" ",,A2:A10)," ","</m><m>")&"</m></k>","//m[.=number()]"),r,ROWS(a)/2,SUMPRODUCT(INDEX(a,SEQUENCE(r,,,2)),INDEX(a,SEQUENCE(r,,2,2))))

but this will only work in 2021, not 2019

 

[lrobbo314]

Another way

EXCEL
	M	N
24	4 M 10 AD	40
25	20 M 7 AD	140
Sheet2

Cell Formulas
Range	Formula
N24:N25	N24	=PRODUCT(IFERROR(TEXTSPLIT(M24," ")+0,1))

 

[Fluff]

The OP does not have textsplit.

 

[lrobbo314]

The OP does not have textsplit.

Ah. Well, an alternative for posterity sake. I'm just bored and having fun.

 

[awoohaw]

delete

 

[cadandcode]

You could try

=LET(a,FILTERXML("<k><m>"&SUBSTITUTE(TEXTJOIN(" ",,A2:A10)," ","</m><m>")&"</m></k>","//m[.=number()]"),r,ROWS(a)/2,SUMPRODUCT(INDEX(a,SEQUENCE(r,,,2)),INDEX(a,SEQUENCE(r,,2,2))))

but this will only work in 2021, not 2019

Does 365 contain all of the coding/formula content such as you shared above?

 

[Peter_SSs]

Given that post 6 indicates all values have the same structure (post 4 worked with semicolon) I think these may also be options.
Not sure if the B2 formula would work as-is in 2021 or might need Ctrl+Shift+Enter, otherwise I think C2 should work in both your versions (once you swap commas for semi-colons)

23 01 22.xlsm
	A	B	C
1
2	4 M 10 AD	206	206
3	20 M 7 AD
4
5	13 M 2 AD
6
cadandcode

Cell Formulas
Range	Formula
B2	B2	=LET(x,SUBSTITUTE(SUBSTITUTE(0&A2:A100," AD",""),"M",REPT(" ",20)),SUM(LEFT(x,20)*RIGHT(x,20)))
C2	C2	=SUMPRODUCT(LEFT(SUBSTITUTE(SUBSTITUTE(0&A2:A100," AD",""),"M",REPT(" ",20)),20)*RIGHT(SUBSTITUTE(SUBSTITUTE(0&A2:A100," AD",""),"M",REPT(" ",20)),20))

 

[cadandcode]

Given that post 6 indicates all values have the same structure (post 4 worked with semicolon) I think these may also be options.
Not sure if the B2 formula would work as-is in 2021 or might need Ctrl+Shift+Enter, otherwise I think C2 should work in both your versions (once you swap commas for semi-colons)

23 01 22.xlsm
	A	B	C
1
2	4 M 10 AD	206	206
3	20 M 7 AD
4
5	13 M 2 AD
6
cadandcode

Cell Formulas
Range	Formula
B2	B2	=LET(x,SUBSTITUTE(SUBSTITUTE(0&A2:A100," AD",""),"M",REPT(" ",20)),SUM(LEFT(x,20)*RIGHT(x,20)))
C2	C2	=SUMPRODUCT(LEFT(SUBSTITUTE(SUBSTITUTE(0&A2:A100," AD",""),"M",REPT(" ",20)),20)*RIGHT(SUBSTITUTE(SUBSTITUTE(0&A2:A100," AD",""),"M",REPT(" ",20)),20))

My main language is Turkish, so C3 (the formula in B2 cell didnt work due version differecen, you are right) cell gave me #AD? (#NAME? in English) error. I changed excel's language to English and solved problem but I cant keep using Excel in English. Have to change it to Turkish back. So how to solve that #NAME? (#AD?) error in Turkish with that original formula you've given in C3 cell?