Okay, I'm getting a lot closer to getting a macro to extract out dates from a text string.
Here's two different macros that are getting me there; however, I need to get some of the 2nd macro into the 1st so it more or less loops and grabs all dates (instead of just one)
here they are:
Code:
Function GetDate(strInput As String) As Date
Dim DateFormat() As String
Dim intDateLength As Integer
Dim intMaxFormat As Integer
Dim intFrmtCtr As Integer
Dim intPosition As Integer
intMaxFormat = 6
ReDim DateFormat(1 To intMaxFormat)
DateFormat(1) = "*##[-/]##[/-]####*"
DateFormat(2) = "*#[-/]##[-/]####*"
DateFormat(3) = "*##[-/]#[-/]##*"
DateFormat(4) = "*##[-/]##[-/]##*"
DateFormat(5) = "*#[-/]##[-/]##*"
DateFormat(6) = "*#[-/]#[-/]##*"
GetDate = Now
For intFrmtCtr = 1 To intMaxFormat
If strInput Like DateFormat(intFrmtCtr) Then
intDateLength = Len(DateFormat(intFrmtCtr)) - 8
strInput = Replace(strInput, " ", "")
For intPosition = 1 To Len(strInput)
If Mid(strInput, intPosition, intDateLength) Like DateFormat(intFrmtCtr) Then
GetDate = DateValue(Mid(strInput, intPosition, intDateLength))
Exit Function
End If
Next intPosition
End If
Next intFrmtCtr
End Function
2nd macro
1) select B2:E2
2) =getdate2(A2)
3) confirm with Ctrl + Shift + Enter (array formula)
Code:
Function getdate2(txt As String)
Dim m As Object, myTxt As String
With CreateObject("VBScript.RegExp")
.Pattern = "(\d{1,2}(/|-)){2}(\d{2}|\d{4})"
.Global = True
For Each m In .execute(txt)
myTxt = myTxt & "," & m.Value
Next
End With
getdate2 = Split(Mid$(myTxt,2),",")
End Function
With this second macro It works great when there are multiple dates:
ie.
Let's say A2 = Reopened for additional information. //PRP 10-27-06 Reopened for adjudication.//pv 12-15-06
When I highlight B2:E2 and then type in the formula =getdate2(A2) and press CTRL+SHIFT+ENTER (array formula). It fills in B2 & C2 with 10-27-06 and 12-15-06 respectively; unfortunately D2 & E2 get a #N/A.
I would like to figure out how to resolve that with just a blank
If let's say A2 =
Reopened for additional information. //PRP 10-27-06 (which obviously only has one date), and I perform this same function Highlight B2:E2 & then type in =getdate2(A2), press CTRL+SHIFT+ENTER(array formula). It fills in B2:E2 all with 10-27-06.
Can one of you EXPERTS please take a look at both of these Macros and see if you can help me. I really need to get the dates automatically extracted from this text string field, else it's a very long manual process of manually typing out the dates. I know it can be done, because I'm so close with these two macros, I can just feel it.
