VBA code to split by multiple delimiters and calculate

[Qroozn]

Hey Guru's.
I hope you can assist.

I am trying to write a UDF and am going around in circles. there is one input which will be a text string ( a different textstring in different cells).
=MYUDF(textstring)

the textstring might look like any of the below (as examples)
bottle
bottle+blueCap
bottle+bluecap*caseQty
bottle+bluecap*3

I would like to have the UDF
1) split the text string by the + and * symbols
2) do a Xlookup of each delimited entry (if it is a number then it can leave the number) against SheetXlookup!A:A and return SheetXlookup!:B:B ( which will likely be a Decimal or Integer)
4) and then have the UDF then calculate the VALUE. (by running sets of brackeets from Left to Right)
e.g. ((bottle + bluecap) * caseQty)

I hope someone can assist to get me started.
thanks in advance

 

[Micron]

If you already have something started as you indicate, posting that between code tags might help others to help you. I'm not strong with Excel functions so will leave the Xlookup thing alone. However I imagine you're familiar with the Split function and maybe Replace() as well. I would Replace() those symbols with something that your data does not use. If you use comma or semicolon for thousands separator and your data might contain one or the other, then don't use either of those with Replace(). A redirect pipe is commonly used since it almost is never part of the data. An example might be

textstring = Replace(Replace(textstring, "+", "|"), "*", "|")

Then you can use Split to divide the portions by using the | as the separator. If you have many more symbols then I'd probably split that into several code lines. A lot of nested Replace() functions can get messy.

 

[DanteAmor]

1) split the text string by the + and * symbols

The following function applies only to those operators.

2) do a Xlookup of each delimited entry

ok

4) and then have the UDF then calculate the VALUE. (by running sets of brackeets from Left to Right)

This will be from left to right.

According to the above, use the following UDF: MyUDF

Put all code in a module:

Function MyUDF(s1 As String)
  Dim op$, cad$
  Dim s2 As Variant
  Dim i&, j&, mases&, pores&, n1&, n2&
  Dim dic As Object
  Dim x As Double
  Dim c As Range
 
  Set dic = CreateObject("Scripting.Dictionary")
 
  With Sheets("SheetXlookup")
    For Each c In .Range("A1", .Range("A" & Rows.Count).End(3))
      If c.Value <> "" Then
        dic(LCase(c.Value)) = c.Offset(0, 1).Value
      End If
    Next
  End With
 
  mases = Len(s1) - Len(Replace(s1, "+", ""))
  pores = Len(s1) - Len(Replace(s1, "*", ""))
 
  If mases + pores > 1 Then
    Do While True
      n1 = InStr(1, s1, "+")
      n2 = InStr(1, s1, "*")
      If n1 > 0 Then
        If n2 > n1 Then
          s1 = EvaluateString(dic, s1, n2, "+")
        Else
          If n2 = 0 Then
            s1 = EvaluateString(dic, s1, Len(s1) + 1, "+")
          Else
            s1 = EvaluateString(dic, s1, n1, "*")
          End If
        End If
      Else
        n2 = InStr(1, s1, "*")
        n2 = InStr(n2 + 1, s1, "*")
        s1 = EvaluateString(dic, s1, n2, "*")
      End If
      mases = Len(s1) - Len(Replace(s1, "+", ""))
      pores = Len(s1) - Len(Replace(s1, "*", ""))
      If mases + pores < 2 Then Exit Do
    Loop
  End If
   
  If mases = 1 Then op = "+" Else op = "*"
  For Each s2 In Split(s1, op)
    If Not IsNumeric(s2) Then
      x = dic(LCase(s2))
      cad = Replace(s1, s2, x)
      s1 = cad
    End If
  Next
 
  MyUDF = Evaluate(s1)
End Function

Function EvaluateString(dic, s1 As String, n As Long, op As String)
  Dim cad As String, s2 As Variant, x As Double, resul As Double
 
  cad = Left(s1, n - 1)
  For Each s2 In Split(cad, op)
    If Not IsNumeric(s2) Then
      x = dic(LCase(s2))
      cad = Replace(cad, s2, x)
    End If
  Next
  resul = Evaluate(cad)
  EvaluateString = resul & Mid(s1, n)
End Function

Works for 1 or more elements:

varios 29oct2024.xlsm
	A	B	C	D	E	F
1	String	Value			textstring	MyUDF
2	bottle	4			bottle	4
3	bluecap	0.5			bottle+blueCap	4.5
4	caseQty	0.25			1+bottle	5
5	other	2			bottle*3	12
6	quin	3.3			bottle*other	8
7					bottle+bluecap*caseQty	1.125
8					bottle+bluecap*3	13.5
9					bluecap*caseQty+2	2.125
10					bluecap*caseQty+other	2.125
11					bluecap+caseQty+other	2.75
12					bluecap*caseQty*caseqty	0.03125
13					bluecap*5*caseqty	0.625
14					bottle+bluecap*caseQty+other	3.125
15					bottle*bluecap*caseQty*other	1
16					bottle+bluecap+caseQty+other*quin	22.275
17					bottle*bluecap*caseQty*other+quin	4.3
18					bottle+bluecap+caseQty+other+quin	10.05
19					bottle*bluecap*caseQty*other*quin	3.3
20
SheetXlookup

Cell Formulas
Range	Formula
F2:F19	F2	=MyUDF(E2)

 

[DanteAmor]

After some revisions, I give you the reduced UDF.
Note: The same rules apply (operators "+", "*" and from left to right)

Function MyUDF(s1 As String)
  Dim s2, n1&, n2&, dic As Object, c As Range
  
  Set dic = CreateObject("Scripting.Dictionary")
  For Each c In Sheets("SheetXlookup").Range("A1", Sheets("SheetXlookup").Range("A" & Rows.Count).End(3))
    If c.Value <> "" Then dic(LCase(c.Value)) = c.Offset(0, 1).Value
  Next
  
  For Each s2 In Split(Replace(Replace(s1, "+", "|"), "*", "|"), "|")
    If Not IsNumeric(s2) Then s1 = Replace(s1, s2, dic(LCase(s2)), , , vbTextCompare)
  Next

  Do While (Len(s1) - Len(Replace(s1, "+", ""))) + (Len(s1) - Len(Replace(s1, "*", ""))) > 1
    n1 = InStr(1, s1, "+")
    n2 = InStr(1, s1, "*")
    If n1 > 0 Then
      If n2 > n1 Then
        s1 = Evaluate(Left(s1, n2 - 1)) & Mid(s1, n2)
      Else
        If n2 = 0 Then
          s1 = Evaluate(Left(s1, Len(s1) + 1 - 1)) & Mid(s1, Len(s1) + 1)
        Else
          s1 = Evaluate(Left(s1, n1 - 1)) & Mid(s1, n1)
        End If
      End If
    Else
      s1 = Evaluate(Left(s1, Len(s1) + 1 - 1)) & Mid(s1, Len(s1) + 1)
    End If
  Loop
    
  MyUDF = Evaluate(s1)
End Function

varios 29oct2024.xlsm
	A	B	C	D	E	F
1	String	Value			textstring	MyUDF
2	bottle	4			bottle	4
3	bluecap	0.5			bottle+blueCap	4.5
4	caseQty	0.25			1+bottle	5
5	other	2			bottle*3	12
6	quin	3.3			bottle*other	8
7					bottle+bluecap*caseQty	1.125
8					bottle+bluecap*3	13.5
9					bluecap*caseQty+2	2.125
10					bluecap*caseQty+other	2.125
11					bluecap+caseQty+other	2.75
12					bluecap*caseQty*caseqty	0.03125
13					bluecap*5*caseqty	0.625
14					bottle+bluecap*caseQty+other	3.125
15					bottle*bluecap*caseQty*other	1
16					bottle+bluecap+caseQty+other*quin	22.275
17					bottle*bluecap*caseQty*other+quin	4.3
18					bottle+bluecap*caseQty+other+quin	6.425
19					bottle*bluecap+caseQty*other*quin	14.85
20					bottle*bluecap*caseQty*other*quin	3.3
SheetXlookup

Cell Formulas
Range	Formula
F2:F20	F2	=MyUDF(E2)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

In relation to the above, since you have XLOOKUP you apparently have a recent Excel version. If that happens to be Microsoft 365 then you may not even need a UDF.
Using the same provisos as Dante - that is

(operators "+", "*" and from left to right)

- you could try using these available worksheet functions.
I have given two options.

• In col F the formula needs to be copied down the column to the bottom of the col E data.
• In col G the formula is a bit longer but does not need to be copied down the column.

Qroozn.xlsm
	A	B	C	D	E	F	G
1	String	Value			textstring
2	bottle	4			bottle	4	4
3	bluecap	0.5			bottle+blueCap	4.5	4.5
4	caseQty	0.25			1+bottle	5	5
5	other	2			bottle*3	12	12
6	quin	3.3			bottle*other	8	8
7					bottle+bluecap*caseQty	1.125	1.125
8					bottle+bluecap*3	13.5	13.5
9					bluecap*caseQty+2	2.125	2.125
10					bluecap*caseQty+other	2.125	2.125
11					bluecap+caseQty+other	2.75	2.75
12					bluecap*caseQty*caseqty	0.03125	0.03125
13					bluecap*5*caseqty	0.625	0.625
14					bottle+bluecap*caseQty+other	3.125	3.125
15					bottle*bluecap*caseQty*other	1	1
16					bottle+bluecap+caseQty+other*quin	22.275	22.275
17					bottle*bluecap*caseQty*other+quin	4.3	4.3
18					bottle+bluecap+caseQty+other+quin	10.05	10.05
19					bottle*bluecap*caseQty*other*quin	3.3	3.3
Sheet2

Cell Formulas
Range	Formula
G2:G19	G2	=BYROW(E2:E19,LAMBDA(r,REDUCE(0,TEXTSPLIT("+"&SUBSTITUTE(SUBSTITUTE(r,"+","|+"),"*","|*"),"|"),LAMBDA(a,x,LET(t,MID(x,2,99),v,XLOOKUP(t,A2:A6,B2:B6,t),IF(LEFT(x,1)="+",a+v,a*v))))))
F2:F19	F2	=REDUCE(0,TEXTSPLIT("+"&SUBSTITUTE(SUBSTITUTE(E2,"+","|+"),"*","|*"),"|"),LAMBDA(a,x,LET(t,MID(x,2,99),v,XLOOKUP(t,A$2:A$6,B$2:B$6,t),IF(LEFT(x,1)="+",a+v,a*v))))
Dynamic array formulas.

 

[bebo021999]

Furthermore, if there are other operators, like exponentiation, e.g., bottle + other^2, what would your result be?

Therefore, I suggest using EVALUATE for the operator string, as follows:

Option Explicit
Function MyFormula(rng As Range, myString As String) As Double
Dim cell As Range
Dim mString As String
mString = UCase(myString)
For Each cell In rng
    If Not IsNumeric(cell.Value) Then
        If InStr(1, mString, UCase(cell.Value)) > 0 Then
            mString = WorksheetFunction.Substitute(mString, UCase(cell.Value), cell.Offset(0, 1).Value)
        End If
    End If
Next cell
MyFormula = Evaluate("=" & mString)
End Function

Book1.xlsm
	A	B	C	D	E	F	G
1	String	Value			textstring	MyUDF
2	bottle	4			bottle	4
3	bluecap	0.5			bottle+blueCap	4.5
4	caseQty	0.25			1+bottle	5
5	other	2			bottle*3	12
6	quin	3.3			bottle*other	8
7					bottle+bluecap*caseQty	4.125
8					(bottle+bluecap)*3	13.5	WITH BRACKET
9					bottle+bluecap*3	5.5	WITHOUT BRACKET
10					bluecap*caseQty+other	2.125
11					bluecap+caseQty+other	2.75
12					bluecap*caseQty*caseqty	0.03125
13					bluecap*5*caseqty	0.625
14					bottle+bluecap*caseQty+other	6.125
15					bottle*bluecap*caseQty*other	1
16					bottle+bluecap+caseQty+other*quin	11.35
17					bottle*bluecap*caseQty*other+quin	4.3
18					bottle+bluecap*caseQty+other+quin	9.425
19					bottle*bluecap+caseQty*other*quin	3.65
20					bottle*bluecap*caseQty*other*quin	3.3
21					bottle + other^2	8	test for other formula
Sheet2

Cell Formulas
Range	Formula
F2:F21	F2	=MyFormula($A$2:$B$6,E2)

 

[DanteAmor]

Hi bebo021999

The operations, according to the OP's rule, must be from left to right, for example:
bottle+bluecap*caseQty = 4+0.5*0.25
=4.5*0.25
=1.125

 

[bebo021999]

Hi bebo021999
View attachment 118723

The operations, according to the OP's rule, must be from left to right, for example:
bottle+bluecap*caseQty = 4+0.5*0.25
=4.5*0.25
=1.125

The OP should follow the rule: inside the brackets () first, as I advised in previous post, because my UDF can control that.