Extracting Date value from text field 1

[Wes1961]

Below is a sample of data (all in one field):

OUTSIDE Need by 7/16/04
NEED BY DATE: MAY 17, 2004
NEED BY DATE 5-28-04
DATE 1-30-04 (NEED ASAP)
18005 NEED BY DATE 2-23-04 ASAP!
NEED BY: ASAP
NEED BY DATE MARCH 1, 2004
NEED BY DATE: FEBRUARY 6, 2004
NEED BY DATE 2/02/04 !!!!!!
need to send UPS red - next day

I need to be able to extract the date portion/value from this field. As you can see it can be almost any format and in any location within the field. Also, some will not have a date and that is Ok.

Can someone help me?

Thanks,
Wes

 

[gol4]

Something like this will need alot more work but should give you the idea

Public Function ParseDate(mystring As String) As Variant
Dim intx As Integer
Dim inty As Integer
For intx = Len(mystring) To 1 Step -1
If IsNumeric(Mid(mystring, intx)) Then
For inty = 1 To intx
If IsDate(Mid(mystring, inty, intx)) Then
ParseDate = Mid(mystring, inty, intx)
Exit Function
End If
Next
End If
Next intx
End Function

?parsedate("want march 4 2004")

 

[gol4]

Revision to above had a few problems with mid function

Public Function ParseDate(mystring As String) As Variant
Dim intx As Integer
Dim inty As Integer
For intx = Len(mystring) To 1 Step -1
If IsNumeric(Mid(mystring, intx, 1)) Then
For inty = 1 To intx
If IsDate(Mid(mystring, inty, intx - inty)) Then
ParseDate = Mid(mystring, inty, (intx - inty) + 1)
Exit Function
End If
Next
End If
Next intx
End Function

ran it thru each of your examples with date in it above and it returned the date