Regex (1 Viewer)

Micron

AWF VIP
Local time
Today, 06:22
Joined
Oct 20, 2018
Messages
3,476
First, when you cross post (post the same issue in different forums) it's considered polite to provide a link to each other site in both of your threads.
Second, General Forum isn't really the place for this question given that you asked for a vba solution elsewhere.

You didn't really put forth much effort in either forum, but even less here. Post more than one example of what the data looks like along with the answers for that data, otherwise the answer you get (based on your other thread) might be that it's just 2 to the power of 3 (2 cubed) which = 8.
 
Last edited:

Micron

AWF VIP
Local time
Today, 06:22
Joined
Oct 20, 2018
Messages
3,476
I do love a challenge though. Since you asked for a vba solution (maybe Regex would be better) this expression might do the trick:
Val(str) * Val(Mid(str, InStr(str, "(") + 1)) * Val(Mid(str, InStr(str, "(") + 1 + Len(CStr(Val(Mid(str, InStr(str, "(") + 1)))) + 1))
 

Alessandro Giraldi

New member
Local time
Today, 11:22
Joined
Feb 24, 2020
Messages
15

Val(str) * Val(Mid(str, InStr(str, "(") + 1)) * Val(Mid(str, InStr(str, "(") + 1 + Len(CStr(Val(Mid(str, InStr(str, "(") + 1)))) + 1))

yes this code is ok for this expression 2(2a2b), but it find only = 8 and not how many a and b ...anyway if i change the expression as 2(2a2b)2c2a2b4(2c2d) the result should be
a = 6
b = 6
c = 10
d = 8
total = 30
i can have a different expression any time so i need a function to calculate the expression.
somebody can help me?
tk u
 

ebs17

Well-known member
Local time
Today, 11:22
Joined
Feb 7, 2020
Messages
1,883
i can have a different expression any time
Codes are implementations of rules, and a rule will only be able to take into account what is known.
For "also different" you need a magician or a crystal ball.

In addition, the first rule in a database would be to work with atomic information from table fields.
This is much easier to calculate than with compound strings.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:22
Joined
May 21, 2018
Messages
8,463
When you say Via calculation do you mean calculating a printed circuit board Via. If so there are javascripts available. See link

You could build what is shown all the formulas are there


If you need help on building that, I can help. I do a lot of math focused databases.
 

Alessandro Giraldi

New member
Local time
Today, 11:22
Joined
Feb 24, 2020
Messages
15
When you say Via calculation do you mean calculating a printed circuit board Via. If so there are javascripts available. See link

You could build what is shown all the formulas are there


If you need help on building that, I can help. I do a lot of math focused databases.
Hi,
Tk u for tour answer.
No, i mean vba code ti calculate One string every time could be differenti.
I work in textile Sector.
Some time the expression can be like 2(2a2b) and the result must be 4a and 4b and the Total of the expression Is 8 or some time could be 2(2a2b2c)2a5b6c3(3c3b)
In every time i have a string likes those i have ti calculate the sum of each letter and the Total without letter.
I don' t know of i have explaned well
Tk u
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:22
Joined
May 21, 2018
Messages
8,463
My fault. I did not figure out the typo. You put Via and I guess that was meant to be Vba. Can you explain the source of these equations? Where does this data come from? Maybe there is a better way. I think this would be a pretty hard algorithm to code if the possibilities for the letters are limitless and the form of the equations are limitless. Even then, my guess the best you could get would be a 80% solution.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:22
Joined
Jan 23, 2006
Messages
15,364
Your explanation is still not clear to me. You have given us some sort of function/algorithm but no context. It appears that some sort of arithmetic/calculaltion is required, but readers would need to know more about the a, b and the values they represent. In plain English what do a,b,c etc represent?
 

Alessandro Giraldi

New member
Local time
Today, 11:22
Joined
Feb 24, 2020
Messages
15
Hi all,
i have tried to put img about my table and what i would like to do in the mask.
my system is access 2016 and the code i need to put under vba access.
the column is "Nota Ordito" and the column "Lettera" rappresent the value i have to give for each letter in the string "Nota Ordito".
i know is not easy, i think about regular expression but probabilly i am wrongin about it.
let me know if is more clear now or you need some more information.
tk u
 

Attachments

  • MAS.JPG
    MAS.JPG
    28.4 KB · Views: 96
  • TBL1.JPG
    TBL1.JPG
    248.5 KB · Views: 100

jdraw

Super Moderator
Staff member
Local time
Today, 06:22
Joined
Jan 23, 2006
Messages
15,364
"Lettera" in your graphic is alphabetic???
Do you mean that in the Nota Ordito (expression) that you replace/substitute each Lettera with the corresponding value in Ordito Nr. Numeric terms are used to multiply the substituted values.

Update: Just saw your latest jpg. Why are there multiple expressions Nota Ordito in some records of TBL1.
 

Attachments

  • Ordito.PNG
    Ordito.PNG
    179.6 KB · Views: 92

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:22
Joined
May 21, 2018
Messages
8,463
After rereading and looking at the data this looks doable. I will take a shot.
 

ebs17

Well-known member
Local time
Today, 11:22
Joined
Feb 7, 2020
Messages
1,883
Since RegEx was in demand, here's a little gimmick to break down a expression so that detailed information can be written into a table so that calculations can be carried out on it.
Code:
Private pRegEx As Object

Public Property Get oRegEx(Optional Reset As Boolean) As Object
   If (pRegEx Is Nothing) Then Set pRegEx = CreateObject("Vbscript.Regexp")
   If Reset Then Set pRegEx = Nothing
   Set oRegEx = pRegEx
End Property

Public Function RegExMatchCollection(ByVal SourceText As String, _
      ByVal SearchPattern As String, _
      Optional ByVal bIgnoreCase As Boolean = True, _
      Optional ByVal bGlobal As Boolean = True, _
      Optional ByVal bMultiLine As Boolean = True) As Object

   With oRegEx
      .Pattern = SearchPattern
      .IgnoreCase = bIgnoreCase
      .Global = bGlobal
      .MultiLine = bMultiLine
      Set RegExMatchCollection = .Execute(SourceText)
   End With
End Function

Public Function RegExReplace(ByVal SourceText As String, _
      ByVal SearchPattern As String, _
      ByVal ReplaceText As String, _
      Optional ByVal bIgnoreCase As Boolean = True, _
      Optional ByVal bGlobal As Boolean = True, _
      Optional ByVal bMultiLine As Boolean = True) As String
 
   With oRegEx
      .Pattern = SearchPattern
      .IgnoreCase = bIgnoreCase
      .Global = bGlobal
      .MultiLine = bMultiLine
      RegExReplace = .Replace(SourceText, ReplaceText)
   End With
End Function

Calling routine
Code:
Sub test_textil()
    Const cT1 = "2(2a2b2c)2a16b6c3(3c3b)"
    'Const cT2 = "2(2A2B)"
    Dim oMC As Object, oMC2 As Object
    Dim oM As Object, oM2 As Object
    Dim sF As String, lF As Long

    Set oMC = RegExMatchCollection(cT1, "((\d+\((\d+[a-z])+\))|(\d+[a-z])+)")
    For Each oM In oMC
        'Debug.Print oM.Value, oM.FirstIndex + 1, oM.Length
        sF = oM.Value
        sF = RegExReplace(sF, "(\d+)\(.+\)", "$1")
        'Debug.Print sF
        If InStr(oM.Value, "(") > 0 Then
            lF = Val(sF)
        Else
            lF = 1
        End If
        'Debug.Print lF
        Set oMC2 = RegExMatchCollection(oM.Value, "\d+[a-z]")
        For Each oM2 In oMC2
            Debug.Print lF, Val(oM2.Value), Replace(oM2.Value, Val(oM2.Value), "")
        Next
    Next
End Sub

Eberhard
 
Last edited:

Alessandro Giraldi

New member
Local time
Today, 11:22
Joined
Feb 24, 2020
Messages
15
Sorry
Since RegEx was in demand, here's a little gimmick to break down a expression so that detailed information can be written into a table so that calculations can be carried out on it.
Code:
Private pRegEx As Object

Public Property Get oRegEx(Optional Reset As Boolean) As Object
   If (pRegEx Is Nothing) Then Set pRegEx = CreateObject("Vbscript.Regexp")
   If Reset Then Set pRegEx = Nothing
   Set oRegEx = pRegEx
End Property

Public Function RegExMatchCollection(ByVal SourceText As String, _
      ByVal SearchPattern As String, _
      Optional ByVal bIgnoreCase As Boolean = True, _
      Optional ByVal bGlobal As Boolean = True, _
      Optional ByVal bMultiLine As Boolean = True) As Object

   With oRegEx
      .Pattern = SearchPattern
      .IgnoreCase = bIgnoreCase
      .Global = bGlobal
      .MultiLine = bMultiLine
      Set RegExMatchCollection = .Execute(SourceText)
   End With
End Function

Public Function RegExReplace(ByVal SourceText As String, _
      ByVal SearchPattern As String, _
      ByVal ReplaceText As String, _
      Optional ByVal bIgnoreCase As Boolean = True, _
      Optional ByVal bGlobal As Boolean = True, _
      Optional ByVal bMultiLine As Boolean = True) As String

   With oRegEx
      .Pattern = SearchPattern
      .IgnoreCase = bIgnoreCase
      .Global = bGlobal
      .MultiLine = bMultiLine
      RegExReplace = .Replace(SourceText, ReplaceText)
   End With
End Function

Calling routine
Code:
Sub test_textil()
    Const cT1 = "2(2a2b2c)2a16b6c3(3c3b)"
    'Const cT2 = "2(2A2B)"
    Dim oMC As Object, oMC2 As Object
    Dim oM As Object, oM2 As Object
    Dim sF As String, lF As Long

    Set oMC = RegExMatchCollection(cT1, "((\d+\((\d+[a-z])+\))|(\d+[a-z])+)")
    For Each oM In oMC
        'Debug.Print oM.Value, oM.FirstIndex + 1, oM.Length
        sF = oM.Value
        sF = RegExReplace(sF, "(\d+)\(.+\)", "$1")
        'Debug.Print sF
        If InStr(oM.Value, "(") > 0 Then
            lF = Val(sF)
        Else
            lF = 1
        End If
        'Debug.Print lF
        Set oMC2 = RegExMatchCollection(oM.Value, "\d+[a-z]")
        For Each oM2 In oMC2
            Debug.Print lF, Val(oM2.Value), Replace(oM2.Value, Val(oM2.Value), "")
        Next
    Next
End Sub

Eberhard
Hi and tk u very much, can i ask how can help pass the result to label
 

ebs17

Well-known member
Local time
Today, 11:22
Joined
Feb 7, 2020
Messages
1,883
I can't do anything with your fragmentary requests. So I say: RegEx cannot write in labels.

I had only tried to extract individual information from such data conglomerates in such a way that you could write it into a table and then continue to count on it.

You should request ready-made solutions from someone who is adequately rewarded and who knows the entire task. The information chunk by chunk is unbearable (for me).

Eberhard
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:22
Joined
May 21, 2018
Messages
8,463
Solution provided
Code:
Public Type t_Ordito
 A As Integer
 B As Integer
 C As Integer
 D As Integer
 E As Integer
 Total As Integer
End Type

Public Function GetOrdito(strOrdito As String) As t_Ordito
  Dim Ordito As t_Ordito
  Dim character As String
  Dim strnumberBefore As String
  Dim numberBefore As Integer
  Dim distributeNumber As Integer
  Dim distribute As Boolean
  Dim i As Integer
  distributeNumber = 1
  For i = 1 To Len(strOrdito)
    character = Mid(strOrdito, i, 1)
    Debug.Print "Char in beginning" & character
    If IsNumeric(character) Then
      strnumberBefore = strnumberBefore & character
      numberBefore = CInt(strnumberBefore)
      
    Else
    If distribute = False Then
     distributeNumber = 1
    Else
     distributeNumber = numberBefore
    End If
   ' Debug.Print "Char " & character & "Distribute " & distribute & " number " & distributeNumber & " Number before " & numberBefore
    Select Case character
    
     Case "A"
      Ordito.A = Ordito.A + numberBefore * distributeNumber
      Debug.Print Ordito.A & " A"
      strnumberBefore = ""
     Case "B"
      Ordito.B = Ordito.B + numberBefore * distributeNumber
      strnumberBefore = ""
     Case "C"
      Ordito.C = Ordito.C + numberBefore * distributeNumber
      strnumberBefore = ""
     Case "D"
      Ordito.D = Ordito.D + numberBefore * distributeNumber
       strnumberBefore = ""
     Case "E"
      Ordito.E = Ordito.E + numberBefore * distributeNumber
      strnumberBefore = ""
     Case "("
      distribute = True
      distribute = numberBefore
      strnumberBefore = ""
     Case ")"
       distribute = False
       distributeNumber = 1
       strnumberBefore = ""
      End Select
    End If
  Next i
  Ordito.Total = Ordito.A + Ordito.B + Ordito.C + Ordito.D + Ordito.E
  GetOrdito = Ordito
 
 
End Function
Code:
Public Sub TestOrditio()
  Dim Ordito As t_Ordito
  Ordito = GetOrdito("2(2a2b2c)2a5b6c3(3c3b)")
  Debug.Print "A = " & Ordito.A
  Debug.Print "B = " & Ordito.B
  Debug.Print "C = " & Ordito.C
  Debug.Print "D = " & Ordito.D
  Debug.Print "E = " & Ordito.E
  Debug.Print "Totol = "; Ordito.Total
End Sub

Output
Code:
A = 6
B = 18
C = 19
D = 0
E = 0
Total =  43

It was actually easier than I thought. You would then have to either do the insert query or you could wrap in individual functions for sql. like
Code:
Public Function GetOrditoA(strOrdito As String) As Integer
  Dim ordito As t_Ordito
  ordito = GetOrdito(strOrdito)
  GetOrditoA = ordito.A
End Function
 

Alessandro Giraldi

New member
Local time
Today, 11:22
Joined
Feb 24, 2020
Messages
15
Solution provided
Code:
Public Type t_Ordito
A As Integer
B As Integer
C As Integer
D As Integer
E As Integer
Total As Integer
End Type

Public Function GetOrdito(strOrdito As String) As t_Ordito
  Dim Ordito As t_Ordito
  Dim character As String
  Dim strnumberBefore As String
  Dim numberBefore As Integer
  Dim distributeNumber As Integer
  Dim distribute As Boolean
  Dim i As Integer
  distributeNumber = 1
  For i = 1 To Len(strOrdito)
    character = Mid(strOrdito, i, 1)
    Debug.Print "Char in beginning" & character
    If IsNumeric(character) Then
      strnumberBefore = strnumberBefore & character
      numberBefore = CInt(strnumberBefore)
     
    Else
    If distribute = False Then
     distributeNumber = 1
    Else
     distributeNumber = numberBefore
    End If
   ' Debug.Print "Char " & character & "Distribute " & distribute & " number " & distributeNumber & " Number before " & numberBefore
    Select Case character
   
     Case "A"
      Ordito.A = Ordito.A + numberBefore * distributeNumber
      Debug.Print Ordito.A & " A"
      strnumberBefore = ""
     Case "B"
      Ordito.B = Ordito.B + numberBefore * distributeNumber
      strnumberBefore = ""
     Case "C"
      Ordito.C = Ordito.C + numberBefore * distributeNumber
      strnumberBefore = ""
     Case "D"
      Ordito.D = Ordito.D + numberBefore * distributeNumber
       strnumberBefore = ""
     Case "E"
      Ordito.E = Ordito.E + numberBefore * distributeNumber
      strnumberBefore = ""
     Case "("
      distribute = True
      distribute = numberBefore
      strnumberBefore = ""
     Case ")"
       distribute = False
       distributeNumber = 1
       strnumberBefore = ""
      End Select
    End If
  Next i
  Ordito.Total = Ordito.A + Ordito.B + Ordito.C + Ordito.D + Ordito.E
  GetOrdito = Ordito


End Function
Code:
Public Sub TestOrditio()
  Dim Ordito As t_Ordito
  Ordito = GetOrdito("2(2a2b2c)2a5b6c3(3c3b)")
  Debug.Print "A = " & Ordito.A
  Debug.Print "B = " & Ordito.B
  Debug.Print "C = " & Ordito.C
  Debug.Print "D = " & Ordito.D
  Debug.Print "E = " & Ordito.E
  Debug.Print "Totol = "; Ordito.Total
End Sub

Output
Code:
A = 6
B = 18
C = 19
D = 0
E = 0
Total =  43

It was actually easier than I thought. You would then have to either do the insert query or you could wrap in individual functions for sql. like
Code:
Public Function GetOrditoA(strOrdito As String) As Integer
  Dim ordito As t_Ordito
  ordito = GetOrdito(strOrdito)
  GetOrditoA = ordito.A
End Function
Tk u very much,

this code is ok but if i have this case (ex. 3(2a2b) doesn' t work, the result is A=4 and B=4.
do you know why?

tk u
 

Users who are viewing this thread

Top Bottom