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

 

[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

Kitap3.xlsx
	D	E	F
10		2.2 AD 4
11		4 M 10 AD	2 M 2 AD
12			2 M 2 AD
13		6 AD 2 M
14
15	2 AD 6 M 3
16			#DEĞER!
Sayfa1

Cell Formulas
Range	Formula
F16	F16	=SUMPROD(D10:F15)

Formula calculates the cells on only 1 column. Formula gives VALUE error when I change array to more than 1 columns. Also I would like to withdraw 1 cell from that formula array which formula includes. Like =(SUMPROD(A1:B4)-B3)

 

[Peter_SSs]

Glad you got XL2BB going, that should help.

Formula calculates the cells on only 1 column. Formula gives VALUE error when I change array to more than 1 columns.

Yes, the function was written for data as set out in all your samples given throughout the thread (single column) so not too surprising that a suggested solution gives an error for a different situation.

You have not explained how data in multiple columns should be treated. For the sample data shown in post #61, what is the expected result and how did you get it calculating manually? Please set out the step/calculations clearly.

Also I would like to withdraw 1 cell from that formula array which formula includes. Like =(SUMPROD(A1:B4)-B3)

Could you post (XL2BB) a representative set of sample data for A1:B4, provide the expected result and explain how that expected result is calculated manually?

 

[cadandcode]

Glad you got XL2BB going, that should help.


Yes, the function was written for data as set out in all your samples given throughout the thread (single column) so not too surprising that a suggested solution gives an error for a different situation.

You have not explained how data in multiple columns should be treated. For the sample data shown in post #61, what is the expected result and how did you get it calculating manually? Please set out the step/calculations clearly.



Could you post (XL2BB) a representative set of sample data for A1:B4, provide the expected result and explain how that expected result is calculated manually?

In that sheet, as you see I would dont like to get 6*2 calculation on purpose, it is independant variable that I will choose which to add in the calculation wherever it is.

Kitap3.xlsx.xlsm
	D	E	F	G	H	I
10		2.2 AD 4
11		4 M 10 AD	2 M 2 AD
12			2 M 2 AD
13		6 AD 2 M
14
15	2 AD 6 M 3
16			=SUMPROD(D10:F15)
17		what it should do -->	=(2,2*4)+(4*10)+(2*2)+(2*2)+(2*6*3)
18
Sayfa1

 

[Peter_SSs]

Thanks for the extra sample and calculation. See if this could work for you.

Function SUMPROD(r As Range, Optional rExcl As Range) As Double
  Dim s As String
  
  s = Application.TextJoin("+", True, r)
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "([A-Z]+)(?=\+|$)"
    If r.Count = 1 Then
      s = .Replace(s & "+0", "")
    Else
      s = .Replace(s & "+0", "")
    End If
    .Pattern = "[A-Z]+"
    SUMPROD = Evaluate(.Replace(s, "*"))
    If Not rExcl Is Nothing Then SUMPROD = SUMPROD - SUMPROD(rExcl)
  End With
End Function

cadandcode.xlsm
	D	E	F
10		2.2 AD 4
11		4 M 10 AD	2 M 2 AD
12			2 M 2 AD
13		6 AD 2 M
14
15	2 AD 6 M 3
16			92.8
Sheet2

Cell Formulas
Range	Formula
F16	F16	=SUMPROD(D10:F15,E13)

 

[cadandcode]

@Peter_SSs Works great. I appreciate your help sir.

 

[Peter_SSs]

@Peter_SSs Works great. I appreciate your help sir.

Sorry, I posted a draft code, not my final. Please try/use this instead.

Function SUMPROD(r As Range, Optional rExcl As Range) As Double
  Dim s As String
  
  s = Application.TextJoin("+", True, r)
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "([A-Z]+)(?=\+|$)"
    s = .Replace(s & "+0", "")
    .Pattern = "[A-Z]+"
    SUMPROD = Evaluate(.Replace(s, "*"))
    If Not rExcl Is Nothing Then SUMPROD = SUMPROD - SUMPROD(rExcl)
  End With
End Function

 

[cadandcode]

Kitap3.xlsx.xlsm
	F
15	337 -- 338
16	675
Sayfa1

Cell Formulas
Range	Formula
F16	F16	=SUMPROD(F15)

Kitap3.xlsx.xlsm
	F
14	339 -- 340
15	337 -- 338
16	1354
Sayfa1

Cell Formulas
Range	Formula
F16	F16	=SUMPROD(F14:F15)

Peter, it SUMed numbers.

 

[Peter_SSs]

Peter, it SUMed numbers.

The function was written for the specifications you provided previously. This is a completely different data format. You can't just keep changing the requirements and expect suggestions to work.

Nor can you expect helpers to continually put effort into providing solutions if they are just going to be told they have wasted their time because the requirements have changed.

 

[cadandcode]

The function was written for the specifications you provided previously. This is a completely different data format. You can't just keep changing the requirements and expect suggestions to work.

Nor can you expect helpers to continually put effort into providing solutions if they are just going to be told they have wasted their time because the requirements have changed.

I understand.