dthis works. I assumed data started in column A1. Also assume code with
semicolons was in column B.

Sub parsecolumns()

Const oldsheet = "Sheet1"
Const newsheet = "sheet2"

Dim MyYear As String

Sheets(oldsheet).Activate
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set DataRange = Range(Cells(1, 1), Cells(LastRow, 1))

Sheets(newsheet).Cells(1, 1) = "Year"
Sheets(newsheet).Cells(1, 2) = "Code"

RowCount = 2
For Each cell In DataRange

MyYear = Left(cell.Value, 4)
CodeString = cell.Offset(rowoffset:=0, columnoffset:=1).Value

Do While InStr(CodeString, ";") 0

NewCode = Left(CodeString, InStr(CodeString, ";") - 1)
CodeString = Mid(CodeString, InStr(CodeString, ";") + 1)

Sheets(newsheet).Cells(RowCount, 1) = MyYear
Sheets(newsheet).Cells(RowCount, 2) = NewCode

RowCount = RowCount + 1

Loop

Sheets(newsheet).Cells(RowCount, 1) = MyYear
Sheets(newsheet).Cells(RowCount, 2) = CodeString
RowCount = RowCount + 1

Next cell


End Sub


"BerkshireGuy" wrote:

I have rows that look like this

20050901 2343;3432
20060823 2232;2321
20050401 2322;4434

I want to parse the values into a separate sheet.

New sheet should look like:

Year Code
-------------------------
2005 2343
2005 3432
2006 2232
2006 2321
2005 2322
2005 4434

Any help would be appreciated.

Thanks