Macro For Splitting Dates

[Dazzawm]

I have a file which is 300,000 rows, and I need a code that will split the dates into other columns. Below is the before and after results that are required. Thanks

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76px;" /><col style="width:64px;" /><col style="width:39px;" /><col style="width:39px;" /><col style="width:23px;" /><col style="width:39px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:12pt; text-align:left; ">Before</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; text-align:left; ">After</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; "> </td><td style="font-size:12pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:12pt; text-align:left; ">Column Z</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; text-align:left; ">N</td><td style="font-size:12pt; text-align:left; ">O</td><td style="font-size:12pt; text-align:left; ">P</td><td style="font-size:12pt; text-align:left; ">Q</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:12pt; text-align:left; ">2001-2003</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; text-align:left; ">01</td><td style="font-size:12pt; text-align:left; ">2001</td><td style="font-size:12pt; text-align:left; ">12</td><td style="font-size:12pt; text-align:left; ">2003</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:12pt; text-align:left; ">2001-2003</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; text-align:left; ">01</td><td style="font-size:12pt; text-align:left; ">2001</td><td style="font-size:12pt; text-align:left; ">12</td><td style="font-size:12pt; text-align:left; ">2003</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:12pt; text-align:left; ">2003-2006</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; text-align:left; ">01</td><td style="font-size:12pt; text-align:left; ">2003</td><td style="font-size:12pt; text-align:left; ">12</td><td style="font-size:12pt; text-align:left; ">2006</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:12pt; text-align:left; ">2003-2006</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; text-align:left; ">01</td><td style="font-size:12pt; text-align:left; ">2003</td><td style="font-size:12pt; text-align:left; ">12</td><td style="font-size:12pt; text-align:left; ">2006</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:12pt; text-align:left; ">2003-</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; text-align:left; ">01</td><td style="font-size:12pt; text-align:left; ">2003</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:12pt; text-align:left; ">2003-</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; text-align:left; ">01</td><td style="font-size:12pt; text-align:left; ">2003</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:12pt; text-align:left; ">2003-</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; text-align:left; ">01</td><td style="font-size:12pt; text-align:left; ">2003</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:12pt; text-align:left; ">2003-</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; text-align:left; ">01</td><td style="font-size:12pt; text-align:left; ">2003</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:12pt; text-align:left; ">1992-1998</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; text-align:left; ">01</td><td style="font-size:12pt; text-align:left; ">1992</td><td style="font-size:12pt; text-align:left; ">12</td><td style="font-size:12pt; text-align:left; ">1998</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:12pt; text-align:left; ">1992-1998</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; text-align:left; ">01</td><td style="font-size:12pt; text-align:left; ">1992</td><td style="font-size:12pt; text-align:left; ">12</td><td style="font-size:12pt; text-align:left; ">1998</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:12pt; text-align:left; ">1992-1998</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; text-align:left; ">01</td><td style="font-size:12pt; text-align:left; ">1992</td><td style="font-size:12pt; text-align:left; ">12</td><td style="font-size:12pt; text-align:left; ">1998</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:12pt; text-align:left; ">1992-1998</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; text-align:left; ">01</td><td style="font-size:12pt; text-align:left; ">1992</td><td style="font-size:12pt; text-align:left; ">12</td><td style="font-size:12pt; text-align:left; ">1998</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:12pt; text-align:left; ">1993-2007</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; text-align:left; ">01</td><td style="font-size:12pt; text-align:left; ">1993</td><td style="font-size:12pt; text-align:left; ">12</td><td style="font-size:12pt; text-align:left; ">2007</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:12pt; text-align:left; ">2005-2009</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; text-align:left; ">01</td><td style="font-size:12pt; text-align:left; ">2005</td><td style="font-size:12pt; text-align:left; ">12</td><td style="font-size:12pt; text-align:left; ">2009</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:12pt; text-align:left; ">1978-1986</td><td style="font-size:12pt; "> </td><td style="font-size:12pt; text-align:left; ">01</td><td style="font-size:12pt; text-align:left; ">1978</td><td style="font-size:12pt; text-align:left; ">12</td><td style="font-size:12pt; text-align:left; ">1986</td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040;

 

[Sahak]

Hi Dazwm:

I don’t know what you have in columns N & P but on cell O3 you can enter formula =LEFT(Z3,4) and on cell Q3 formula =RIGHT (Z3,4)

 

[Dazzawm]

I need the start and end months not just the years as the 'after' shows. The first year range starts at the month 01 and the last year range ends at the month 12

 

[Sahak]

I'm Sorry, I don’t understand what you want to get in cell: 01 & year, in other cell: 12 & year? Your example shows only years.

 

[Dazzawm]

Looking at row 3. Column N shows month 01 (January) then the first year 2001 in 'O', then the end month 12 (December) in column 'P' and the last year 2003 in 'Q'. I would rather have a code then a formula because there are so many rows and columns and data, I dont want to mess about moving columns or inserting helper columns etc.. Thanks.

 

[hiker95]

dazwm,


I assume that your raw data begins in cell Z3, and that each cell always contains 4 digits and a dash or 4 digits and a dash and 4 digits.


Sample data before the macro:

Excel Workbook
	N	O	P	Q	Z
3					2001-2003
4					2001-2003
5					2003-2006
6					2003-2006
7					2003-
8					2003-
9					2003-
10					2003-
11					1992-1998
12					1992-1998
13					1992-1998
14					1992-1998
15					1993-2007
16					2005-2009
17					1978-1986
18
Sheet1

After the macro:]/b]

Excel Workbook
	N	O	P	Q	Z
3	01	2001	12	2003	2001-2003
4	01	2001	12	2003	2001-2003
5	01	2003	12	2006	2003-2006
6	01	2003	12	2006	2003-2006
7	01	2003			2003-
8	01	2003			2003-
9	01	2003			2003-
10	01	2003			2003-
11	01	1992	12	1998	1992-1998
12	01	1992	12	1998	1992-1998
13	01	1992	12	1998	1992-1998
14	01	1992	12	1998	1992-1998
15	01	1993	12	2007	1993-2007
16	01	2005	12	2009	2005-2009
17	01	1978	12	1986	1978-1986
18
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SplitZ()
' hiker95, 07/05/2011
' http://www.mrexcel.com/forum/showthread.php?t=562110
Dim c As Range, a As Long, Sp, s As Long
Application.ScreenUpdating = False
For Each c In Range("Z3", Range("Z" & Rows.Count).End(xlUp))
  Select Case Len(Trim(c))
    Case 5
      With Cells(c.Row, "N")
        .NumberFormat = "@"
        .Value = "01"
      End With
      With Cells(c.Row, "O")
        .NumberFormat = "@"
        .Value = Left(c, 4)
      End With
    Case 9
      Sp = Split(Trim(c), "-")
      With Cells(c.Row, "N")
        .NumberFormat = "@"
        .Value = "01"
      End With
      With Cells(c.Row, "O")
        .NumberFormat = "@"
        .Value = Sp(0)
      End With
      With Cells(c.Row, "P")
        .NumberFormat = "@"
        .Value = "12"
      End With
      With Cells(c.Row, "Q")
        .NumberFormat = "@"
        .Value = Sp(1)
      End With
  End Select
Next c
Application.ScreenUpdating = True
End Sub

Then run the SplitZ macro.

 

[Dazzawm]

Thanks hiker95 it worked like a dream and didnt take 2 mins for 300,000 rows. The data actually starts in Z3 but I just copied the headers. Thanks again.