Pulling out lastest date in line of text

[Donna]

Hi,
I was wondering if it would be possible to do the following.
This would be used in a Macro.

If I had the following in a cell:
10/20/09 sent statement* 9/10/09 rec ck # 1358 $ 4000.00 sent to lb.
8/15/09 inv 135950 $700.00 can pending credits.

The problem is that the date is not always the first thing in the cell and I
am looking for the latest date in the text.

Is there a way to pull out just the latest date in the text in one column
and then the next column would be the text that follows the latest date.
As in the example above
1st column would have 9/10/09
2nd column would have rec ck # 1358 $4000.00 sent to lb. and the rest of the
text.
Thanks for looking for this
Donna Hauff

 

[Jacob Skaria]

--Do you have the below text in one cell? or in two cells?

10/20/09 sent statement* 9/10/09 rec ck # 1358 $ 4000.00 sent to lb.
8/15/09 inv 135950 $700.00 can pending credits.

--In your example the latest date is 10/20/09 isnt it.

Dim strData As String, arrData As Variant, varDate As Variant
strData = Range("A1")
arrData = Split(strData)
For intTemp = 0 To UBound(arrData)
If IsDate(arrData(intTemp)) Then
If CDate(arrData(intTemp)) > varDate Then varDate = arrData(intTemp)
End If
Next

-- Do you mean the last date in text? If so try the below...

Dim strData As String, arrData As Variant, varDate As Variant
strData = Range("A1")
arrData = Split(strData)
For intTemp = 0 To UBound(arrData)
If IsDate(arrData(intTemp)) Then varDate = arrData(intTemp)
Next

PS: This could be rewritten to suit your requirement...Before that please
confirm witgh few more examples..

If this post helps click Yes

 

[Jacob Skaria]

You mean the latest date or last date in text...Try the below macro..for
both...

Sub Macro()
Dim strData As String, arrData As Variant, varDate As Variant
strData = Range("A1")
arrData = Split(strData)
'if you are looking for the last date in text string
For intTemp = 0 To UBound(arrData)
If IsDate(arrData(intTemp)) Then varDate = arrData(intTemp)
Next

'OR if you are looking for the latest date
'For intTemp = 0 To UBound(arrData)
'If IsDate(arrData(intTemp)) Then
'If CDate(arrData(intTemp)) > vardate Then vardate = arrData(intTemp)
'End If
'Next
MsgBox varDate

MsgBox Mid(strData, InStr(1, strData, varDate, _
vbTextCompare) + Len(varDate) + 1)

End Sub

If this post helps click Yes

 

[Ron Rosenfeld]

Hi,
I was wondering if it would be possible to do the following.
This would be used in a Macro.

If I had the following in a cell:
10/20/09 sent statement* 9/10/09 rec ck # 1358 $ 4000.00 sent to lb.
8/15/09 inv 135950 $700.00 can pending credits.

The problem is that the date is not always the first thing in the cell and I
am looking for the latest date in the text.

Is there a way to pull out just the latest date in the text in one column
and then the next column would be the text that follows the latest date.
As in the example above
1st column would have 9/10/09
2nd column would have rec ck # 1358 $4000.00 sent to lb. and the rest of the
text.
Thanks for looking for this
Donna Hauff

From your examples, I am assuming you mean the "last" date entered into the
string, and not the "latest" date in time.

That being the case, this VBA Macro should do what you want, placing the
desired data into the adjacent columns.

To enter this Macro (Sub), <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 Macro (Sub), first select the range of cells to be processed. Then
<alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

===========================
Option Explicit
Sub SplitData()
Dim c As Range, rg As Range
Dim s As String
Dim re As Object

Set rg = Selection

Set re = CreateObject("vbscript.regexp")
re.Global = True
'pattern picks out valid dates in m/d/y or mm/dd/yyyy formats
'make sure next is all on a single line
re.Pattern = ".*(\b(0?[1-9]|1[012])[\- /.](0?[1-9]|[12][0-9]|3[01])[\-
/.](19|20)?[0-9]{2}\b)\s(.*)"
For Each c In rg
s = c.Value
c.Offset(0, 1).Value = re.Replace(s, "$1")
c.Offset(0, 2).Value = re.Replace(s, "$5")
Next c
End Sub
==================================
--ron

 

[Donna]

Hi Ron,
I did mean latest date in the cell. I am so sorry, I had the wrong date
as my answer, it should have been 10/20/09 as the latest date. I did try the
one below and it worked great, just not the date I need. If it would be
possible could I add one thing, and I promise I will try to be more accurate
with my typing.
In some cases I will have such as the following:
Permanent Exception*10/20/09 stmt*9/05/09 inv 1245 can per request
Could it put everything before the latest date, then the latest date, and
then all the information after the latest date. So it would be
1st column Permanent Exception*
2nd column 10/20/09 *(yes, I am sure this time)
3rd column *9/05/09 inv 1245 can per request
Again, thank you so much for your help and I sorry I did not give you the
correct information the 1st time. Excel is amazing and the more I learn the
more I realize how very little I know. Thanks Donna

Hi,
I was wondering if it would be possible to do the following.
This would be used in a Macro.

If I had the following in a cell:
10/20/09 sent statement* 9/10/09 rec ck # 1358 $ 4000.00 sent to lb.
8/15/09 inv 135950 $700.00 can pending credits.

The problem is that the date is not always the first thing in the cell and I
am looking for the latest date in the text.

Is there a way to pull out just the latest date in the text in one column
and then the next column would be the text that follows the latest date.
As in the example above
1st column would have 9/10/09 (should be 10/20/09)
2nd column would have rec ck # 1358 $4000.00 sent to lb. and the rest of the
text.
Thanks for looking for this
Donna Hauff

From your examples, I am assuming you mean the "last" date entered into the
string, and not the "latest" date in time.

That being the case, this VBA Macro should do what you want, placing the
desired data into the adjacent columns.

To enter this Macro (Sub), <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 Macro (Sub), first select the range of cells to be processed. Then
<alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

===========================
Option Explicit
Sub SplitData()
Dim c As Range, rg As Range
Dim s As String
Dim re As Object

Set rg = Selection

Set re = CreateObject("vbscript.regexp")
re.Global = True
'pattern picks out valid dates in m/d/y or mm/dd/yyyy formats
'make sure next is all on a single line
re.Pattern = ".*(\b(0?[1-9]|1[012])[\- /.](0?[1-9]|[12][0-9]|3[01])[\-
/.](19|20)?[0-9]{2}\b)\s(.*)"
For Each c In rg
s = c.Value
c.Offset(0, 1).Value = re.Replace(s, "$1")
c.Offset(0, 2).Value = re.Replace(s, "$5")
Next c
End Sub
==================================
--ron
.

 

[Ron Rosenfeld]

In some cases I will have such as the following:
Permanent Exception*10/20/09 stmt*9/05/09 inv 1245 can per request
Could it put everything before the latest date, then the latest date, and
then all the information after the latest date. So it would be
1st column Permanent Exception*
2nd column 10/20/09 *(yes, I am sure this time)
3rd column *9/05/09 inv 1245 can per request

I think what you want to do is "split" on the latest date. However, in your
example, the third column is NOT everything after the latest date, but rather
it omits the substring "stmt"

If what you really want is what you wrote, and not how you typed your example,
then try this:

==================================
Option Explicit
Sub SplitOnLatestDate()
Dim c As Range, rng As Range
Dim re As Object, m As Object, mc As Object
Dim dt As Date, s As String, d As String

Set rng = Selection
Set re = CreateObject("vbscript.regexp")
re.Global = True

For Each c In rng
s = c.Value
Range(c.Offset(0, 1), c.Offset(0, 3)).ClearContents
dt = 0

'ensure next statement is all on a single line
re.Pattern = "(\b(0?[1-9]|1[012])[- /.](0?[1-9]|[12]\d|3[01])[-
/.](19|20)?\d{2}\b)"

If re.test(s) = True Then
Set mc = re.Execute(s)
For Each m In mc
If DateValue(m) > dt Then
dt = DateValue(m)
d = m
End If
Next m

re.Pattern = "(.*\b)" & d & "(\b.*)"
Set mc = re.Execute(s)
c.Offset(0, 1) = Trim(mc(0).submatches(0))
'Decide how you want the "latest date" displayed
c.Offset(0, 2).NumberFormat = "dd mmm yyyy"
c.Offset(0, 2) = d
c.Offset(0, 3) = Trim(mc(0).submatches(1))
End If
Next c

Range(rng.Offset(0, 1), rng.Offset(0, 3)).EntireColumn.AutoFit

End Sub
==================================
--ron