Extracting Numbers from Alphanumeric Text

[Laura2008]

Hi all, I am trying to parse some alphanumeric data for inventory purposes and am having a hard time pulling out the numbers. Please see below for what I'm working with. The end goal is to see "TOTAL UNITS" ie for the first one 4, 720 (ie 60*12), 6,1, 25, 100 I have seen VBA/etc. for pulling out text, but none for numbers. PLEASE help! Thanks!

4/CA</SPAN>

60 WIPES/CN 12 CN/CA </SPAN>

6 EA/CA </SPAN>

1 CA/CA </SPAN>

25 EA/BX </SPAN>

25 EA/BX 4 BX/CA </SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[RoryA]

Hi, and welcome to the forum.

Add this function to a normal module:

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(n)
            Next n
         End If
      End If
   End With

End Function

then use it like this:
=GetQuantity(A1)

 

[Laura2008]

Thank you!! My only issues with this formula are for if the data name has a number in the name for example

2-PLY 200 BX/CA </SPAN>

400</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=2></COLGROUP>

When in fact there are only 200 units, the tissues are just "2-ply" is that something I'll just have to go through and manually fix?

 

[RoryA]

Are there set rules that can be applied? If not, some manual intervention will be required.