try this code

Sub seperate_semicolon()

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
RowCount = 1
Do While Range("A" & RowCount) < ""
SemiColonStr = Trim(Range("B" & RowCount))
Do While Len(SemiColonStr) 0
If InStr(SemiColonStr, ";") 0 Then
Number = Left(SemiColonStr, InStr(SemiColonStr, ";") - 1)
SemiColonStr = Mid(SemiColonStr, InStr(SemiColonStr, ";") + 1)
Rows(RowCount + 1).Insert
Range("B" & RowCount) = Number
Range("A" & RowCount + 1) = Range("A" & RowCount)
Range("C" & RowCount + 1) = Range("C" & RowCount)
RowCount = RowCount + 1
Else
Number = SemiColonStr
Range("B" & RowCount) = Number
SemiColonStr = ""
End If
Loop

RowCount = RowCount + 1
Loop

End Sub

"Joe K." wrote:


I have a spreadsheet in the format listed below. I would like to
spreadsheet each number separated by semi colons into a new records.

Please help me with excel VBA code to complete this task.

Thanks,


Column A Column B Column C
Colorado 1;4;6;11;25 Denver
Florida 24;38;44;12 Miami
Illinois 13;7;8;9;10 Chicago


Desire Output
Column A Column B Column C
Colorado 1 Denver
Colorado 4 Denver
Colorado 6 Denver
Colorado 11 Denver
Colorado 25 Denver
Florida 24 Miami
Florida 38 Miami
Florida 44 Miami
Florida 12 Miami
Illinois 13 Chicago
Illinois 7 Chicago
Illinois 8 Chicago
Illinois 9 Chicago
Illinois 10 Chicago