grab amt of days from cells

[scott2000]

I have a column of cells with due dates, that look like:

5 days prior
2 days prior
2 weeks prior
1-3 days prior

I need to get the number of days, and change if its listed as weeks, or in the case of the 1-3 (or similar), the larger of the numbers...

I am clueless on this one! I appreciate any help!

cheers.

 

[scott2000]

I've come up with below to get rid of the 'days prior' or 'weeks prior' to give me just the numbers, but I don't know how to deal with the cells that say
4-7 prior days
since when i strip 'prior days' off, it gives me a large number instead.

Here is what I have:

    For Each myCell In dRange
    myCell.NumberFormat = "0"
    If Right(myCell, 10) = "days prior" Then
        myCell.Value = Left(myCell, Len(myCell) - 10)
    ElseIf Right(myCell, 9) = "wks prior" Then
        myCell.Value = Left(myCell, Len(myCell) - 9) * 7
    End If
    Next

Any ideas?
Thanks! Thanks! for any help

 

[Scott Huish]

Perhaps:

Sub test()
Dim c As Range, t As Integer
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    t = 1
    If InStr(c, "week") > 0 Then t = 7
    c.Offset = MaxNumber(c.Text) * t
Next
End Sub
 
Function MaxNumber(r As String)
Dim m As Object
With CreateObject("vbscript.regexp")
    .Pattern = "\d+ |-"
    .Global = True
    If .test(r) Then
        Set m = .Execute(r)
        MaxNumber = m(m.Count - 1)
    End If
End With
End Function

 

[scott2000]

You are a god.

thank you.