Parse Date from Text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

How can I parse a text field to pick up just the date. The field has mixed
values. If I could get some guidance in how to look for starting point of
text or number, I could try to use instr and mid but I'm stumped about how to
start the search. Thanks.

01/01/07 Baltimore
02/12/1995 Detroit
Washington 12/15/86
08-12-87 Michigan
08/14/96; Virginia
 
Hello,

How can I parse a text field to pick up just the date. The field has mixed
values. If I could get some guidance in how to look for starting point of
text or number, I could try to use instr and mid but I'm stumped about how to
start the search. Thanks.

01/01/07 Baltimore
02/12/1995 Detroit
Washington 12/15/86
08-12-87 Michigan
08/14/96; Virginia
Click to expand...

If the values are always Date Location:
Left(FieldName],InStr([fieldName]," ")-1)
will return the data before the space.

However your sample data shows one value as the location date, and
another with date; location.

It's best to get your data in the same order, then it's easy to parse
the text.
 
Here's a function that comes close to what you asked for:

Public Function ExtractDate(strIn As String) As Date
'-- Return a Date value from the following strings
'01/01/07 Baltimore
'02/12/1995 Detroit
'Washington 12/15/86
'08-12-87 Michigan
'08/14/96; Virginia

'-- Note: There is nothing in this code to return an error if there is no
valid date
'-- in the string

Dim MyArray As Variant
Dim strDate As String

MyArray = Split(strIn, " ")
If IsNumeric(Left(MyArray(0), 1)) Then
'-- The date is in the first location
strDate = MyArray(0)
Else
'-- The date is in the second location
strDate = MyArray(1)
End If

Do While Not IsNumeric(Right(strDate, 1))
'-- Remove any trailing non numeric chatacters
strDate = Left(strDate, Len(strDate) - 1)
Loop

ExtractDate = DateValue(strDate)

End Function
 
The only change I might make to that is to use the IsDate() function to check
the value of the entire field rather than using the IsNUmeric function (you'd
need to strip the trailing fields like ':' before checking each array value).
Then the date could fall anywhere in the record, even if there are more than
two fields.
 
I forgot to add that you'd need to loop thru the array for that to work,
rather than just check the first two values in the array. If you're sure
you'll never have more than the two values you can just check the first two
array elements.
 
Thanks everybody for solution/suggestions. It works very well. Sometimes
the date can be spelled out like April 23, 1998. How can I handle that since
I cant use " " to split the filed?

Thanks Again.
 
Back
Top