Extract date from cells to right

[shadow12345]

Hi all,

I am trying to extract month information from the below table. What I want to do is extract every date from the columns in C onwards.

Col A tells you how many dates you will find.
Dates are store in C, D, E and so on.

Dates can be found as below (all dates are 1st of the month): After the Word "TEST" and a space you find the year as YY then MM.

TEST 111100000F+1 = 01/Nov/2011
TEST 120900000F-1 = 01/Sep/2012

I would like to return all months in the row in Col B of that row in this format:
MMM/YY - MMM/YY - MMM/YY and so on.

Max number of dates to be found on any row is 20.

Hopefully that clear if not let me know.



<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><td align="center" bgcolor="#C0C0C0"><b>D</b></td><td align="center" bgcolor="#C0C0C0"><b>E</b></td><td align="center" bgcolor="#C0C0C0"><b>F</b></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>1</b></td><td rowspan="1" colspan="1" width="174" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Number </font></td><td rowspan="1" colspan="1" width="357" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Month </font></td><td rowspan="1" colspan="1" width="384" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Leg </font></td><td rowspan="1" colspan="1" width="384" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="384" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="384" 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="174" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2 </font></td><td rowspan="1" colspan="1" width="357" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="384" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">TEST 111100000F+1 </font></td><td rowspan="1" colspan="1" width="384" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">TEST 120900000F-1 </font></td><td rowspan="1" colspan="1" width="384" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="384" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>3</b></td><td rowspan="1" colspan="1" width="174" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">4 </font></td><td rowspan="1" colspan="1" width="357" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="384" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">TEST 151200000F+1 </font></td><td rowspan="1" colspan="1" width="384" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">TEST 160300000F-1 </font></td><td rowspan="1" colspan="1" width="384" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">TEST 160600000F-1 </font></td><td rowspan="1" colspan="1" width="384" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">TEST 160900000F+1 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>4</b></td><td rowspan="1" colspan="1" width="174" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">3 </font></td><td rowspan="1" colspan="1" width="357" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="384" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">TEST 111200000F+1 </font></td><td rowspan="1" colspan="1" width="384" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">TEST 120300000F-2 </font></td><td rowspan="1" colspan="1" width="384" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">TEST 120900000F+1 </font></td><td rowspan="1" colspan="1" width="384" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
</table>

 

[shadow12345]

If it helps this would extract the date from the first cell... i just need to know how to do this on every cell right of Col C in each row

Sub example()

leg = Range("C2").Value

MMMFirst = Mid(leg, 8, 2) & "-"
YYSecond = Mid(leg, 6, 2)
DDThird = "01-"
FMonth = Format(DDThird & MMMFirst & YYSecond, "MMM-YY")


End Sub