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

 

[Peter_SSs]

You have the code in the wrong place. It should be in a standard module, not the ThisWorkbook module

Don't use Ctrl+Shift+Enter

 

[cadandcode]

You have the code in the wrong place. It should be in a standard module, not the ThisWorkbook module

View attachment 83459

Don't use Ctrl+Shift+Enter

Yours worked but shinigami's one gave #VALUE! error

 

[shinigamilight]

cuz i thought u wanted the result of each cell in the cell next to it, not all the ranges in 1 cell just like your example. it'll work on 1 cell at a time not the whole range.

 

[Peter_SSs]

Yours worked

Good news! Thanks for the confirmation. I hope that is some use for you.

 

[cadandcode]

cuz i thought u wanted the result of each cell in the cell next to it, not all the ranges in 1 cell just like your example. it'll work on 1 cell at a time not the whole range.

I need one cell to include the cells in the range that I set, sir.
@Peter_SSs you are welcome sir

 

[Peter_SSs]

I need one cell to include the cells in the range that I set, sir.

That is not clear to me. Do you mean that you need the function to work even if there is only one cell in the range?

If so, is there an option for you to use the function like this, referencing an empty cell below?

cadandcode.xlsm
	A	B
1
2	4 M 10 AD	40
3
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUMPROD(A2:A3)

 

[cadandcode]

@Peter_SSs I would like to explain.
There will be a cell (random cell I will decide) which contains results. Results of that formula calculates as taking numbers only and multiplying them. That 1 cell I tell in the beginning will have all results.
For example; B1 will be result cell. A1 have 4M 10, A2 has 5 KG 4M 10 AD and so. B1 (result cell) will be B1=(4*10)+(5*4*10)
And I will be able to change that B1 (result cell) to another cell. Code should let me change that result cell (B1) to another cell I decided.
Therefore, code should let me decide which rows/columns will be calculated in B1(result cell).
Like there is A1= 4M 10, A2= 10 M 20 KG, A3= 2M 3 AD and I wont add A3 in calculation and want to add another cell in calculation like AK= 40 M 10 AD 20 KG and B1(result cell)= (4*10)+(10*20)+(40*10*20)
Code should let me choose cells. Not calculate all cells in sheet. And there should be 1 cell that calculates also let me choose its place to put.

 

[Peter_SSs]

I have no idea what that was all about. Can you
- post a small set of sample data with XL2BB where the existing function does not produce the expected result
- tell us what the expected result is and why

 

[cadandcode]

I have no idea what that was all about. Can you
- post a small set of sample data with XL2BB where the existing function does not produce the expected result
- tell us what the expected result is and why

Adding XL2BB gave security error

 

[Peter_SSs]

Adding XL2BB gave security error

Have a look at these two threads
Xl2bb got disable
xl2bb