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

--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


"Donna" wrote:

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

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
---------------
Jacob Skaria


"Donna" wrote:

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