Split comma seperated column in to new rows

**singy33** · 08-06-2009, 11:49 AM

Hi,

I have attached an excel document with all postcode and towns in the UK, each row is for a seperate postcode, some postcode rows have multiple towns which are comma seperated.

How would I split the multiple towns up in to seperate rows so that each row only has one town.?

for example teh row below :

B21 Birmingham, Handsworth West Midlands 52.51 -1.94

would become two rows :

B21 Birmingham West Midlands 52.51 -1.94
B21 Handsworth West Midlands 52.51 -1.94

Thanks
Mark

**JBeaucaire** · 08-06-2009, 12:35 PM

Try this macro on your sheet:

Option Explicit

Sub SplitTowns()
'JBeaucaire (8/6/2009)
Dim i As Long
Application.ScreenUpdating = False

Do
    i = i + 1
    If InStr(Cells(i, "B"), ",") > 0 Then
        Rows(i).Copy
        Rows(i + 1).Insert Shift:=xlDown
        Application.CutCopyMode = False
        Cells(i, "B") = Left(Cells(i, "B"), WorksheetFunction.Find(",", Cells(i, "B")) - 1)
        Cells(i + 1, "B") = Right(Cells(i + 1, "B"), Len(Cells(i + 1, "B")) - _
            WorksheetFunction.Find(",", Cells(i + 1, "B")) - 1)
    End If
Loop Until Cells(i + 1, "B") = ""

Application.ScreenUpdating = True
End Sub