Extraction for Ashish Mathur

[Donna]

Hi,
I did have one more question and I wasn't sure since I had rated it if it
would come up again. When I used the formula on these examples

(BOND-NO NEW BONDS) Filed Chapter 11 Bankruptcy 9/15/09
It pulled out in the first column-- 11 and not the expected 9/15/09
and
(PERMANENT EXCEPTION) 3/09 10/05/09 stmt
It pulled out 3/09 instead of the expected 10/05/09

Is there a way to specify only to pull out the date if it is in a date
format 10/27/09?
Thanks Donna

:

Click to show or hide original message or reply text.

 

[Ron Rosenfeld]

Hi,
I did have one more question and I wasn't sure since I had rated it if it
would come up again. When I used the formula on these examples

(BOND-NO NEW BONDS) Filed Chapter 11 Bankruptcy 9/15/09
It pulled out in the first column-- 11 and not the expected 9/15/09
and
(PERMANENT EXCEPTION) 3/09 10/05/09 stmt
It pulled out 3/09 instead of the expected 10/05/09

Is there a way to specify only to pull out the date if it is in a date
format 10/27/09?
Thanks Donna

The macro I provided in your last thread about the same issue works almost as
you described.

Instead of starting multiple threads about the same problem, you would probably
receive more appropriate advice if you kept everything in the same thread and
explained what was wrong with the various solutions you've already been
provided.

For example, the macro I created, with a minor alteration, should ignore the
3/09 (which it interpreted incorrectly anyway):

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

 

[Ashish Mathur]

Check your previous post

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com