I think I'd try to parse it out into its pieces.
first thing I'd do is get rid of the st, nd, rd, th stuff (1st, 2nd, 3rd, 4th,
....).
then look until I found the 2000 surrounded by important stuff.
(I looked for " 20## to ")
then go backwards to pick out the first date and go forward to pick out the
second.
(If your dates go back to 1999 (and before), this'll fail.)
If either failed, then I tossed out a #ref error.
This is the code I used:
Option Explicit
Function twoDates(myCell As Range) As Variant
Dim iCtr As Long
Dim SpacePosBeforeYear As Long
Dim startingSpacePos As Long
Dim endingSpacePos As Long
Dim FoundSpace As Long
Dim myDates(0 To 1) As Date
Dim mySplit As Variant
Dim myStr As String
Dim testDate As Variant
If Application.Caller.Cells.Count <> 2 Then
twoDates = CVErr(xlErrRef)
Exit Function
End If
Set myCell = myCell.Cells(1, 1)
myStr = myCell.Value
myStr = Application.Substitute(myStr, "nd ", " ")
myStr = Application.Substitute(myStr, "st ", " ")
myStr = Application.Substitute(myStr, "rd ", " ")
myStr = Application.Substitute(myStr, "th ", " ")
SpacePosBeforeYear = 0
For iCtr = 1 To Len(myStr)
If Mid(myStr, iCtr, 9) Like " 20## to " Then
SpacePosBeforeYear = iCtr
Exit For
End If
Next iCtr
If SpacePosBeforeYear = 0 Then
twoDates = CVErr(xlErrRef)
Exit Function
End If
FoundSpace = 0
For iCtr = SpacePosBeforeYear - 1 To 1 Step -1
If Mid(myStr, iCtr, 1) = " " Then
FoundSpace = FoundSpace + 1
If FoundSpace = 2 Then
startingSpacePos = iCtr
Exit For
End If
End If
Next iCtr
testDate = Mid(myStr, startingSpacePos + 1, _
(SpacePosBeforeYear + 5) - startingSpacePos - 1)
If IsDate(testDate) Then
myDates(0) = CDate(testDate)
Else
twoDates = CVErr(xlErrRef)
Exit Function
End If
endingSpacePos = 0
For iCtr = SpacePosBeforeYear + 9 To Len(myStr)
If Mid(myStr, iCtr, 1) = " " Then
endingSpacePos = iCtr
Exit For
End If
Next iCtr
If endingSpacePos = 0 Then
twoDates = CVErr(xlErrRef)
Exit Function
End If
testDate = Mid(myStr, SpacePosBeforeYear + 9, _
(endingSpacePos - 2) - (SpacePosBeforeYear + 9) + 1)
mySplit = Split97(testDate, "/")
'check for 3 parts
If UBound(mySplit) - LBound(mySplit) + 1 = 3 Then
myDates(1) = DateSerial(mySplit(UBound(mySplit)) + 2000, _
mySplit(LBound(mySplit) + 1), _
mySplit(LBound(mySplit)))
Else
twoDates = CVErr(xlErrRef)
Exit Function
End If
If Application.Caller.Columns.Count = 2 Then
twoDates = myDates
Else
twoDates = Application.Transpose(myDates)
End If
End Function
'from Tom Ogilvy
Function Split97(sStr As Variant, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function
===============
If you're using xl2k or higher, you can replace:
application.substitute with Replace
and
split97 with split
(and delete that useful function from Tom Ogilvy)
So if your text were in A1, you could highlight A2:A3 (with A2 the activecell)
and type:
=twodates(a1)
and hit ctrl-shift-enter
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
=======
Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)
right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side
Paste the code in there.
Now go back to excel.
Type in that formula in A2:A3 and hit ctrl-shift-enter.
