extracting number from text string and doing calculations

vicasso 

You may simply try the following User Defined Function. Place the following Function on a Standard Module like Module1 and then use it on the Worksheet just like a regular Excel Formula.

Function CustomProduct(ByVal str As String) As Long
Dim Matches As Object
Dim Amount  As Long
Dim Days    As Long

With CreateObject("VBScript.RegExp")
    .Global = False
    .Pattern = "^\$(\d+) for days 1 through (\d+)"
    
    If .test(str) Then
        Set Matches = .Execute(str)
        Amount = Matches(0).submatches(0)
        Days = Matches(0).submatches(1)
        If Days >= 4 Then
            CustomProduct = Amount * 4
        Else
            CustomProduct = Amount * Days
        End If
    End If
End With
End Function

Then you may use it on the Worksheet like this...

In B2

=CustomProduct(A2)