Extract dates from data as strings

[Shadow123]

Hi,

I am trying to find a extract the dates found in the below raw data, and post them one at a time... there will only be a maximum of two of these dates per row.

I got some help yesterday extracting the 5 digit numbers, so im not sure if that can be changed or not, but let me know and ill post that code if needed.

<table border = "1" cellspacing = "0" bordercolor="#999999">
<tr><td bgcolor="#C0C0C0"> </td>
<td align="center" bgcolor="#C0C0C0"><b>A</b></td><td align="center" bgcolor="#C0C0C0"><b>B</b></td><td align="center" bgcolor="#C0C0C0"><b>C</b></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>1</b></td><td rowspan="1" colspan="1" width="493.5" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Results  </font></td><td rowspan="1" colspan="1" width="151.5" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Result </font></td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>2</b></td><td rowspan="1" colspan="1" width="493.5" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">abc.k(p).nov20.99375.65487.12354.jan13.p  </font></td><td rowspan="1" colspan="1" width="151.5" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">nov20 </font></td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">jan13 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>3</b></td><td rowspan="1" colspan="1" width="493.5" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">edf.jun20.99815.p  </font></td><td rowspan="1" colspan="1" width="151.5" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">jun20 </font></td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>4</b></td><td rowspan="1" colspan="1" width="493.5" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">abc.s.jan40.65841  </font></td><td rowspan="1" colspan="1" width="151.5" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">jan40 </font></td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>5</b></td><td rowspan="1" colspan="1" width="493.5" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">tge.s.96514.jan15.96548.Feb15 </font></td><td rowspan="1" colspan="1" width="151.5" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">jan15  </font></td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">feb15 </font></td></tr>
</table>


thanks in advnace for any help

 

[Firefly2012]

Hi

The following formulas will work as long as the month is always followed by two digits (ie jan20 or jan02 but not jan2):

Excel 2007
	A	B	C
1	Result
2	abc.k(p).nov20.99375.65487.12354.jan13.p	nov20	jan13
3	edf.jun20.99815.p	jun20
4	abc.s.jan40.65841	jan40
5	tge.s.96514.jan15.96548.Feb15	jan15	Feb15
Sheet1

Cell Formulas
Range	Formula
B2	{=MID(A2,MIN(SEARCH(TEXT("1-"&ROW(INDIRECT("1:12"))+0,"mmm"),A2&"janfebmaraprmayjunjulaugsepoctnovdec")),5)}
C2	{=MID(A2,MIN(SEARCH(TEXT("1-"&ROW(INDIRECT("1:12"))+0,"mmm"),SUBSTITUTE(A2,B2,"XXXXX",1)&"janfebmaraprmayjunjulaugsepoctnovdec")),5)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Andrew Poulsom]

Adapting yesterday's code try:

Sub Test()
    Dim Rng As Range, Dn As Range
    Dim Nums
    Dim Ac As Integer
    Dim n
    Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    For Each Dn In Rng
        Nums = Split(Dn, ".")
        Ac = 0
        For Each n In Nums
           If LCase(n) Like "[a-z][a-z][a-z]##" Then
                Ac = Ac + 1
                With Dn.Offset(, Ac)
                    .NumberFormat = "@"
                    .Value = n
                End With
            End If
        Next n
    Next Dn
End Sub

 

[Shadow123]

Well both of those options work, im going to poke around with the differences in the VBA code and see if I can at least understand it.

Thanks for the help both of you.

 

[Shadow123]

How could i change it to start putting the data into the first blank cell to the right of A2 .... so if b2 has value then c2 or d2 and so on?

I know its something to do with this line

Ac = 0

I tried this, but it doesn't seem to work thought i might of gotten that one lol



Ac = Range("rng").End(xlToRight).Offset(0, 1).Select

 

[Shadow123]

well i worked out how stupid that was to say.... its an interger for a start lol...

this does it ... With Dn.End(xlToRight).Offset(0, 1)