Extract a number before specific letters & more....

[SaulieDog]

Hi Everyone -
I'm trying to extract the quantity number from the text AND if there isn't a quantity for it to return "1".
My end goal is to divide this number by a price to get the individual unit price.
As you can see sometimes it's at the beginning or end and may be followed by "pack or "/case"
I found some equations that can solve part of what I'm doing but can't figure out how to combine them correctly
I may be looking for the impossible. Any help or advice is appreciated.
Thanks very much.

Price	Description	Unit	Price/unit
$100	10 pack, GAC post-carbon cartridge 1/4" JG black housing	10	$10
$40	Storage Tank / 9 Gallon	1	$40
$600	GAC Carbon Post Filter Cartridge, 3/8 JG, 50 PK	50	$12
$350	RO Module, 30 gpd GE TFM-36A, 25/Case	25	$14

 

[Herakles]

Welcome to Mr Excel

Based upon the data that you have given us this, code will work but more data may identify
rows for which it does NOT work. If this the case then let me know.

Copy this code to a standard code module.

Test it on a copy of your data.

It asks you to select the range that you want to extract the quantity from.

Public Sub subExtractQuantity()
Dim i As Integer
Dim arr() As String
Dim rngSelected As Range
Dim rng As Range
Dim varQty As Variant
Dim varValue As Variant

    ActiveWorkbook.Save
    
    On Error Resume Next
    Set rngSelected = Application.InputBox(Title:="Quantity extraction.", _
      Prompt:="Select the range of cells to extract quantity from.", Type:=8)
    On Error GoTo 0

    If rngSelected Is Nothing Then
        Exit Sub
    End If
    
    If rngSelected.Columns.Count > 1 Then
        Exit Sub
    End If
    
    For Each rng In rngSelected.Cells
    
        varValue = Replace(Replace(rng.Value, "/", ",", 1), " ", ",", 1)
        
        arr = Split(varValue, ",")
        
        If IsNumeric(arr(0)) Then
            varQty = arr(0)
        ElseIf IsNumeric(arr(UBound(arr) - 1)) Then
            varQty = arr(UBound(arr) - 1)
        Else
            varQty = 1
        End If
        
        rng.Offset(0, 1).Value = varQty
    
    Next rng
        
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
A formula option

Fluff.xlsm
	A	B	C	D
1	Price	Description	Unit	Price/unit
2	100	10 pack, GAC post-carbon cartridge 1/4" JG black housing	10	10
3	40	Storage Tank / 9 Gallon	1	40
4	600	GAC Carbon Post Filter Cartridge, 3/8 JG, 50 PK	50	12
5	350	RO Module, 30 gpd GE TFM-36A, 25/Case	25	14
Master

Cell Formulas
Range	Formula
C2:C5	C2	=TEXTAFTER(TRIM(TEXTBEFORE(B2,{"pack"," PK","/case"},,1,,1))," ",-1,,1)
D2:D5	D2	=A2/C2

 

[SaulieDog]

THANK YOU BOTH!!! So far so go!

 

[Fluff]

Glad we could help & thanks for the feedback.