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]

According to an online formula translator, the formula in Turkish would be this. You should be able to copy it directly from the forum by clicking this icon at the top right of the formula box below

=TOPLA.ÇARPIM(SOLDAN(YERİNEKOY(YERİNEKOY(0&A2:A100;" AD";"");"M";YİNELE(" ";20));20)*SAĞDAN(YERİNEKOY(YERİNEKOY(0&A2:A100;" AD";"");"M";YİNELE(" ";20));20))

 

[cadandcode]

According to an online formula translator, the formula in Turkish would be this. You should be able to copy it directly from the forum by clicking this icon at the top right of the formula box below
View attachment 83418

=TOPLA.ÇARPIM(SOLDAN(YERİNEKOY(YERİNEKOY(0&A2:A100;" AD";"");"M";YİNELE(" ";20));20)*SAĞDAN(YERİNEKOY(YERİNEKOY(0&A2:A100;" AD";"");"M";YİNELE(" ";20));20))

If I remove "AD" from cell, formula works but if I remove "M" it gives error. It looks like cells must have "M" and I cant type another parameters. (M is meter and AD is count, result is total meter). I would like to be able to change them short forms of words. For example I will type there 10 kids, each one's weight is 50 kg. My cell will be "50 KG 10 AD" and formula will do it if I remove "AD" but "KG" or "M". What if I dont type words.

 

[Peter_SSs]

if I remove "M" it gives error.

The formula in post 4 also gives an error if you remove "M" but in post 6 you said that formula worked. I based my suggestion on that information.
If your data and questioned has changed, then please provide a new representative set of sample data (5 to 10 rows should be enough) and explain what the expected result is and how you get it.

BTW, I have had somebody save my Mini Sheet sample from post 19 in English Excel then open it in Turkish Excel and the formulas automatically translated to Turkish and the result in C3 was still 206.
The C3 formula was exactly as I gave in post 21.

 

[cadandcode]

The formula in post 4 also gives an error if you remove "M" but in post 6 you said that formula worked. I based my suggestion on that information.
If your data and questioned has changed, then please provide a new representative set of sample data (5 to 10 rows should be enough) and explain what the expected result is and how you get it.

BTW, I have had somebody save my Mini Sheet sample from post 19 in English Excel then open it in Turkish Excel and the formulas automatically translated to Turkish and the result in C3 was still 206.
The C3 formula was exactly as I gave in post 21.

Formula worked but I didnt tested it such removing, changing characters inside cells. Tried your version and saw that "AD" doesnt affect formula but "M".

 

[shinigamilight]

I found a vba code to extract 4 and 10 from that text cell into B1 like "410"

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]

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

Yes it does & so does 2021.

 

[awoohaw]

I am confused, I thought the M and AD being in the same location were a given? What are they being replaced by?
what are the consistent attributes of the text string?

 

[cadandcode]

I am confused, I thought the M and AD being in the same location were a given? What are they being replaced by?
what are the consistent attributes of the text string?

Text string is giving you info about measure/unit of something, like 4 pencils (that is AD) and 3 meters (that is M). I can change that characters to KG (kilograms, weight) or anything else. Main point is whatever I put there independent of variables as characters, formula should take NUMBERS ONLY. And that vars could be more than 2, for example; 4 sticks, 3 meters each, 2 kg each. Cell will look like A1= 4 AD 3 M 2 KG, independent of alphabet characters, I need formula to take 4,3,2 and multiply them eachother in B1 like B1=4*3*2=24. If cell has 2,3,4 or ... different numbers, so on. AD (count), M (meter), KG (weight) and so just independent of variables from formula. I need numbers to be multiplied from cells.

 

[Fluff]

If you are going to have a varying quantity of numbers in a cell, then I think you are better off sticking with the formula in each row of col B & then adding that column up.

 

[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 @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!