Extraction for Ashish Mathur

D

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top