Extract substring if condition is met

[heyJefe]

I am having a problem getting my vending machine sheet to work properly & would appreciate any help.

The sales sheet is usually filled with a copy/paste from an online report (html). Other columns have irrelevant data but I want to extract from the H column. The items are listed in format "00xx(x.xx)". This shows a sale of item 00xx for cost (x.xx). Sometimes there are up to 3 items sold at once possibly with different item numbers & prices ex. "00xx(x.xx), 00xx(x.xx), 00yy(y.yy)"

Costs & product placement vary on different machines but only 1 machine will be analyzed at a time. I would like to extract the price associated with the item sold. I seem to be on the right track but I don't know how to fix this error.

A1 shows product 0000(1.00) & A2 tells me 6 were sold.
B1 & B2 (item 0002) work
C1 does not work properly to tell me with item was sold in C2

Again, any help would be appreciated. Thanks.

[GC Excel]

Hello Jefe,

I'm not sure what the problem is with your formulas, but here's a suggestion using a macro:

Sub SalesAnalysis()
   Dim ar, Dic, a, b
   Dim i As Integer, j As Integer, str
      
   Set Dic = CreateObject("scripting.dictionary")
   
   With Sheets("Sales")
      ar = .Range("H1:H" & .Cells(.Rows.Count, 8).End(xlUp).Row)
      For i = 1 To UBound(ar, 1)
         str = Replace(ar(i, 1), " ", "")
         str = Split(str, ",")
         For j = LBound(str) To UBound(str)
            a = "'" & Split(str(j), "(")(0)
            If Not Dic.exists(a) Then
               Dic.Add a, 1
            Else
               Dic(a) = Dic(a) + 1
            End If
         Next j
      Next i
      
      a = Dic.keys
      b = Dic.items
      
      With .Range("P1")
         .Resize(UBound(a) + 1, 1) = Application.Transpose(a)
         .Offset(0, 1).Resize(UBound(a) + 1, 1) = Application.Transpose(b)
      End With
   End With
End Sub