ExcelBanter - View Single Post

Ron Rosenfeld[_2_]

On Tue, 2 Oct 2012 12:04:02 -0700 (PDT), wrote:

Hello can someone please help me.
I'm exporting a report from a diffrent scource and in one section it has this
"Yesterday ( Oct 1, 2012 12:00:00 AM - Oct 2, 2012 12:00:00 AM )"

Can anyone tell me how I can extract the First date only and paste it to a diffrent cell? In this case it would be Oct 1, 2012 or 10/1/2012
Thank you

I misread your post. If the date in your string could be either Oct 1, 2012 or 10/1/2012 then we need to change the regex in my UDF.

The following will accept dates in the format of mmm d, yyyy or, if the date is between 1/1/1900-12/31/2199 or using 2 digit years; and the separators can be dashes, spaces, forward slashes or dots, the following should extract them. The only difference is the regex: sPat.

======================================
Option Explicit
Function GetFirstDate(s As String)
Dim re As Object, mc As Object
Const sPat As String = "\b(?:(?:Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|N ov|Dec)\s+(?:[1-9]|[1-2]\d|3[01]),\s+\d{4})|\b(0?[1-9]|1[012])[- /.](0?[1-9]|[12][0-9]|3[01])[- /.](19|20|21)?[0-9]{2}\b"
Set re = CreateObject("vbscript.regexp")
With re
.Global = False
.Pattern = sPat
.ignorecase = True
End With
If re.test(s) = True Then
Set mc = re.Execute(s)
GetFirstDate = mc(0)
Else
GetFirstDate = "No date in proper format"
End If
End Function
==================================