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]

Glad we could help & thanks for the feedback.

 

[Fluff]

You cannot use multiple cell in filterxml like that.

 

[cadandcode]

Maybe try this then

Sub Number_Multiplier()
        Dim Regex As Object
        Set Regex = CreateObject("vbscript.regexp")
        Dim k As Integer
        Regex.Global = True
        Regex.Pattern = "[0-9]+"
        lr = Range("A" & Rows.Count).End(xlUp).Row
        Dim mc As Object
        Dim S
        Dim NumRes As Double
       
        For k = 1 To lr
                Set mc = Regex.Execute(Range("A" & k))
                For S = 0 To mc.Count - 1
                        If S = 0 Then
                        NumRes = mc(0)
                        Else: NumRes = NumRes * mc(S)
                        End If
                Next S
                Range("B" & k) = NumRes
        Next k
       
       
End Sub

@Fluff is it possible to add an array controller here? it calculates everysingle cells, I need some cells in sheet to be calculated.

 

[Fluff]

You would need to ask the person who wrote the code.

 

[cadandcode]

@Fluff is it possible to add an array controller here? it calculates everysingle cells, I need some cells in sheet to be calculated.

@shinigamilight

 

[shinigamilight]

Which cells you want to calculate?

 

[cadandcode]

Which cells you want to calculate?

It is not specific. It will be 2 cells or 4 cells another day (depends on the different production we made and I put them in cells)

 

[shinigamilight]

I created a function out it, to install it paste it in the vba editor and type =numbermultiplier in the worksheet and point to the cell.

Function NumberMultiplier(rng As Range)
       
        Dim Regex As Object
        Set Regex = CreateObject("vbscript.regexp")
        Regex.Global = True
        Regex.Pattern = "[0-9]+"
       
        Dim mc As Object
        Dim S
        Dim NumRes As Double
      
       
        Set mc = Regex.Execute(rng.Value)
               
                For S = 0 To mc.Count - 1
                        If S = 0 Then
                        NumRes = mc(0)
                        Else: NumRes = NumRes * mc(S)
                        End If
                Next S
       
        NumberMultiplier = NumRes

      
      
End Function

 

[Peter_SSs]

Another user-defined function to consider.
I have assumed that the data will be in a column and that the range fed into the function will be more than one cell.

Function SUMPROD(r As Range)
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "([A-Z]+)(?=\+|$)"
    SUMPROD = .Replace(Join(Application.Transpose(r), "+") & "+0", "")
    .Pattern = "[A-Z]+"
    SUMPROD = Evaluate(.Replace(SUMPROD, "*"))
  End With
End Function

cadandcode.xlsm
	A	B
1
2	4 M 10 AD	287.3804
3	20 M 7 AD
4
5	13 M 2 AD
6	4 M 10 AD 2 KG
7	3.4 M 2.03 AD 0.2 KG
8
9
10
Sheet1

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

 

[cadandcode]

Not sure why none of your codes worked, pasted vba into table and used function but nothing happened.
@Peter_SSs @shinigamilight
Note: I tried to paste it via ctrl shift enter