Is a macro ok?
Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim mySplit As Variant
Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add
With curWks
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With
With newWks
.Cells.NumberFormat = "@" 'stop them from converting to dates
For Each myCell In myRng.Cells
mySplit = Split97(myCell.Value, "-")
'or if xl2k and higher
'mySplit = Split(myCell.Value, "-")
.Cells(.Rows.Count, CLng(mySplit(LBound(mySplit)))) _
.End(xlUp).Offset(1, 0).Value _
= myCell.Value
.Cells(.Rows.Count, CLng(mySplit(UBound(mySplit)))) _
.End(xlUp).Offset(1, 0).Value _
= myCell.Value
Next myCell
With Intersect(.UsedRange.EntireColumn, .Rows(1))
.NumberFormat = "General"
.Formula = "=column()"
.Value = .Value
End With
End With
End Sub
Function Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function
Split was added in xl2k. If you're using xl97, use Tom's split97. If you and
your users are all at xl2k or higher, you can delete that function completely.
If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm