Option Explicit
Public Sub test()
Const SEARCH_STRING1 As String = "| PUN-6X1-BL | Art Nr.: 159664 | VPE= 1m | Preis= 1,20€"
Const SEARCH_STRING2 As String = "*VPE*"
Const SEARCH_STRING3 As String = "*Preis*"
Dim astrArray() As String
Dim ialngIndex As Long, dblValue1 As Double, dblValue2 As Double
Dim strText As String, strSearch1 As String, strSearch2 As String
strSearch1 = Mid$(String:=SEARCH_STRING2, Start:=2, Length:=Len(SEARCH_STRING2) - 2)
strSearch2 = Mid$(String:=SEARCH_STRING3, Start:=2, Length:=Len(SEARCH_STRING3) - 2)
astrArray = Split(Expression:=SEARCH_STRING1, Delimiter:="|")
For ialngIndex = 0 To UBound(astrArray)
If astrArray(ialngIndex) Like SEARCH_STRING2 Then
dblValue1 = Search_Values(pvstrSearch:=astrArray(ialngIndex))
strText = strText & strSearch1 & " : " & dblValue1 & vbCr
ElseIf astrArray(ialngIndex) Like SEARCH_STRING3 Then
dblValue2 = Search_Values(pvstrSearch:=astrArray(ialngIndex))
strText = strText & strSearch2 & " : " & dblValue2 & vbCr
End If
Next
Call MsgBox(strText, vbExclamation)
End Sub
Public Function Search_Values(ByVal pvstrSearch As String) As Double
Const SEARCH_CHARS1 As String = "= "
Const SEARCH_CHARS2 As String = "m"
Const SEARCH_CHARS3 As String = "€"
Dim alngPos(1 To 4) As Long
alngPos(1) = InStr(1, pvstrSearch, SEARCH_CHARS1)
alngPos(2) = InStr(1, pvstrSearch, SEARCH_CHARS2)
alngPos(3) = InStr(1, pvstrSearch, SEARCH_CHARS3)
alngPos(4) = IIf(Right$(String:=pvstrSearch, Length:=1) = " ", 2, 1)
Search_Values = CDbl(Mid$(String:=pvstrSearch, _
Start:=alngPos(1) + 2, Length:=Len(pvstrSearch) - alngPos(1) - 1 - alngPos(4)))
End Function