Who Wants a Challenge?

[Laura2008]

Hi all-
I am in the middle of a HUGE dataset and having some trouble calculating the correct units of measure value so I can accurately calculate "net cost". An example is below:

Unit Amount: 50 EA/BX 4 BX/CA
Unit of Measure: BX
Cost per Unit: $9.40


I have a code that I have used to parse out and calculate the total units (see below); but I don't know if there is a way to edit this so that I can accurately get the correct amount of "EA" for the Unit of Measure indicated.
(For example: my code correctly gives me the 100 EA p/ CA, but I need to parse out the 50 EA/BX because BX is my Unit of measure). IF ANYONE can help that would be greatly appreciated. Thank you!

This is the VBA Code I currently have to parse my alphanumeric data (ie 50 ea/bx 4 bx/ca)

Function GetQuantity(rIn As Range) As Double
Dim matches
Dim n As Long
With CreateObject("vbscript.regexp")
.Pattern = "[\d]+"
.Global = True
Set matches = .Execute(rIn.Value)
If matches.Count = 0 Then
GetQuantity = 0
Else
GetQuantity = matches(0)
If matches.Count > 1 Then
For n = 1 To matches.Count - 1
GetQuantity = GetQuantity * matches
Next n
End If
End If
End With
End Function