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

Easily done with a User Defined Function.

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

Note that this function depends on the date being in the format shown in your example:
Month as a three letter abbreviation
Date as a number from 1-31 (no leading zero's)
Year as a four digit year

If the format can be more variable, we will need to adjust the regular expression to accomodate.

To use this User Defined Function (UDF), enter a formula like

=GetFirstDate(A1)

in some cell.

=================================
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|Nov| Dec)\s+(?:[1-9]|[1-2]\d|3[01]),\s+\d{4}\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
==========================================