Option Explicit
Public Sub test()
Const SEARCH_STRING2 As String = "VPE"
Const SEARCH_STRING3 As String = "Preis"
Dim dblValue1 As Double, dblValue2 As Double
dblValue1 = Reg_Exp(pvstrSearch:=SEARCH_STRING2)
dblValue2 = Reg_Exp(pvstrSearch:=SEARCH_STRING3)
Call MsgBox(SEARCH_STRING2 & " : " & dblValue1 & vbCr & SEARCH_STRING3 & " : " & dblValue2)
End Sub
Public Function Reg_Exp(ByVal pvstrSearch As String) As Double
Dim objRegEx As Object, objMatch As Object
Dim strText As String
strText = "| PUN-6X1-BL | Art Nr.: 159664 | VPE= 1m | Preis= 1,20€"
Set objRegEx = CreateObject(Class:="vbscript.regexp")
With objRegEx
.Global = True
.IgnoreCase = True
.MultiLine = False
.Pattern = pvstrSearch & "= [+-]?([0-9]*[,])?[0-9]+"
Set objMatch = .Execute(strText)
End With
Reg_Exp = CDbl(Split(Expression:=objMatch(0), Delimiter:="= ")(1))
Set objRegEx = Nothing
Set objMatch = Nothing
End Function