Function SumKg(inputStr As String) As Variant
Dim regEx As Object
Dim matches As Object
Dim match As Variant
Dim sum As Double
Set regEx = CreateObject("VBScript.RegExp")
With regEx
.Global = True
.Pattern = "(\d{1,3}(,\d{3})*\.\d+|\d+)\s*Kg"
End With
Set matches = regEx.Execute(inputStr)
For Each match In matches
sum = sum + CDbl(Replace(match.SubMatches(0), ",", ""))
Next match
SumKg = sum
End Function
Function SumKgCW(inputStr As String) As Variant
Dim regEx As Object
Dim matches As Object
Dim match As Variant
Dim sum As Double
Set regEx = CreateObject("VBScript.RegExp")
With regEx
.Global = True
.Pattern = "(\d{1,3}(,\d{3})*\.\d+|\d+)\s*Kg CW"
End With
Set matches = regEx.Execute(inputStr)
For Each match In matches
sum = sum + CDbl(Replace(match.SubMatches(0), ",", ""))
Next match
SumKgCW = sum
End Function
Function Summ3(inputStr As String) As Variant
Dim regEx As Object
Dim matches As Object
Dim match As Variant
Dim sum As Double
Set regEx = CreateObject("VBScript.RegExp")
With regEx
.Global = True
.Pattern = "(\d{1,3}(,\d{3})*\.\d+|\d+)\s*m3"
End With
Set matches = regEx.Execute(inputStr)
For Each match In matches
sum = sum + CDbl(Replace(match.SubMatches(0), ",", ""))
Next match
Summ3 = sum
End Function
Cell C2 formula , Drag down
Formula:
=SUMkg(SUBSTITUTE(B2,",",))-SumKgCW(B2)
Cell D2 formula , Drag down
Formula:
=Summ3(B2)
Cell E2 formula , Drag down
Formula:
=SumKgCW(B2)
Bookmarks