VBA code to split by multiple delimiters and calculate

Qroozn

Well-known Member
Joined
Mar 12, 2002
Messages
544
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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
VBA Code:
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.
 
Upvote 0
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:
VBA Code:
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
ABCDEF
1StringValuetextstringMyUDF
2bottle4bottle4
3bluecap0.5bottle+blueCap4.5
4caseQty0.251+bottle5
5other2bottle*312
6quin3.3bottle*other8
7bottle+bluecap*caseQty1.125
8bottle+bluecap*313.5
9bluecap*caseQty+22.125
10bluecap*caseQty+other2.125
11bluecap+caseQty+other2.75
12bluecap*caseQty*caseqty0.03125
13bluecap*5*caseqty0.625
14bottle+bluecap*caseQty+other3.125
15bottle*bluecap*caseQty*other1
16bottle+bluecap+caseQty+other*quin22.275
17bottle*bluecap*caseQty*other+quin4.3
18bottle+bluecap+caseQty+other+quin10.05
19bottle*bluecap*caseQty*other*quin3.3
20
SheetXlookup
Cell Formulas
RangeFormula
F2:F19F2=MyUDF(E2)


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

VBA Code:
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
ABCDEF
1StringValuetextstringMyUDF
2bottle4bottle4
3bluecap0.5bottle+blueCap4.5
4caseQty0.251+bottle5
5other2bottle*312
6quin3.3bottle*other8
7bottle+bluecap*caseQty1.125
8bottle+bluecap*313.5
9bluecap*caseQty+22.125
10bluecap*caseQty+other2.125
11bluecap+caseQty+other2.75
12bluecap*caseQty*caseqty0.03125
13bluecap*5*caseqty0.625
14bottle+bluecap*caseQty+other3.125
15bottle*bluecap*caseQty*other1
16bottle+bluecap+caseQty+other*quin22.275
17bottle*bluecap*caseQty*other+quin4.3
18bottle+bluecap*caseQty+other+quin6.425
19bottle*bluecap+caseQty*other*quin14.85
20bottle*bluecap*caseQty*other*quin3.3
SheetXlookup
Cell Formulas
RangeFormula
F2:F20F2=MyUDF(E2)


😅
 
Upvote 0
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
ABCDEFG
1StringValuetextstring
2bottle4bottle44
3bluecap0.5bottle+blueCap4.54.5
4caseQty0.251+bottle55
5other2bottle*31212
6quin3.3bottle*other88
7bottle+bluecap*caseQty1.1251.125
8bottle+bluecap*313.513.5
9bluecap*caseQty+22.1252.125
10bluecap*caseQty+other2.1252.125
11bluecap+caseQty+other2.752.75
12bluecap*caseQty*caseqty0.031250.03125
13bluecap*5*caseqty0.6250.625
14bottle+bluecap*caseQty+other3.1253.125
15bottle*bluecap*caseQty*other11
16bottle+bluecap+caseQty+other*quin22.27522.275
17bottle*bluecap*caseQty*other+quin4.34.3
18bottle+bluecap+caseQty+other+quin10.0510.05
19bottle*bluecap*caseQty*other*quin3.33.3
Sheet2
Cell Formulas
RangeFormula
G2:G19G2=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:F19F2=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.
 
Upvote 0
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:

VBA Code:
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
ABCDEFG
1StringValuetextstringMyUDF
2bottle4bottle4
3bluecap0.5bottle+blueCap4.5
4caseQty0.251+bottle5
5other2bottle*312
6quin3.3bottle*other8
7bottle+bluecap*caseQty4.125
8(bottle+bluecap)*313.5WITH BRACKET
9bottle+bluecap*35.5WITHOUT BRACKET
10bluecap*caseQty+other2.125
11bluecap+caseQty+other2.75
12bluecap*caseQty*caseqty0.03125
13bluecap*5*caseqty0.625
14bottle+bluecap*caseQty+other6.125
15bottle*bluecap*caseQty*other1
16bottle+bluecap+caseQty+other*quin11.35
17bottle*bluecap*caseQty*other+quin4.3
18bottle+bluecap*caseQty+other+quin9.425
19bottle*bluecap+caseQty*other*quin3.65
20bottle*bluecap*caseQty*other*quin3.3
21bottle + other^28test for other formula
Sheet2
Cell Formulas
RangeFormula
F2:F21F2=MyFormula($A$2:$B$6,E2)
 
Last edited:
Upvote 0
Hi bebo021999
1730295236748.png


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

🤗
 
Upvote 0

Forum statistics

Threads
1,223,879
Messages
6,175,148
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top