Hi,
Cells in Column A have strings of the following type:
eg A1:
Billing for the period March 14 2009 to March 13 2010 - Order dated
April 01 2009
In B1 I want the date : 14-Mar-2009
In C1 I want the date: 13-Mar-2010
(to elaborate, only the first two dates in the string need to be
extracted, third and subsequent dates in the string to be ignored)
Thanks in advance for the help.
Regards,
Raj
The possible variety of formats makes this a bit tricky, but if in your last
post you've included all the possible formats, the following UDF should work.
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.
To use this User Defined Function (UDF), enter a formula like
=GetDate(A1,Index)
in some cell.
A1 is any cell reference containing a string which has multiple included dates.
Index is the date instance you wish to extract. (1 for first instance; 2 for
second instance, etc). If Index is greater than the number of dates in the
string, the function will return a #VALUE! error.
Other limitations and assumptions are noted in the code below.
Be sure to format the result as a Date, otherwise you will just see a 5 digit
number.
======================================================
Option Explicit
Function GetDate(s As String, Optional Index As Long = 1) As Date
'returns date entered in a variety of formats as a real Excel date
'Index = the instance of date within a string
'if Index is greater than the number of dates, #VALUE! error
Dim m As String, dy As String, yr As String
m = GetMonth(s, Index)
dy = GetDayYr(s, Index)(0)
yr = GetDayYr(s, Index)(1)
GetDate = DateValue(m & " " & dy & ", " & yr)
End Function
'-------------------------------------------------------------
Private Function GetMonth(s As String, Optional i As Long = 1)
'Assumes at least first three letters of month present
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
re.Pattern = "\b(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)"
If re.test(s) = True Then
Set mc = re.Execute(s)
GetMonth = mc(i - 1).Value
End If
Set re = Nothing
End Function
'-------------------------------------------------------------------
Private Function GetDayYr(s As String, Optional i As Long = 1) As Variant
'assumes that only digits represent day(date) and year; and that day(date)
' always precedes year
Dim re As Object, mc As Object
Dim aTemp(0 To 1)
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\b(\d{1,2}(?=\D))\D+\b(\d{4}|\d{2})\b"
If re.test(s) = True Then
Set mc = re.Execute(s)
aTemp(0) = mc(i - 1).submatches(0)
aTemp(1) = mc(i - 1).submatches(1)
GetDayYr = aTemp
End If
Set re = Nothing
End Function
=========================================
--ron
