Solved: Complex code needed (Seperate words)

[av8tordude]

I need a code that will look into each cell in column B and find each city separated by “/” and separate each city (along with the rest of the information) into its own row. (See Attached)



For example…


California | Antioch / Brentwood / Concord | Contra Costa | $1.00 | $2.00 | $3.00
California | Antioch| Contra Costa | $1.00 | $2.00 | $3.00
California | Brentwood| Contra Costa | $1.00 | $2.00 | $3.00
California | Concord | Contra Costa | $1.00 | $2.00 | $3.00

[p45cal]

try:
[vba]Sub spreadOut()
lastRow = Cells(Rows.count, 1).End(xlUp).Row
For rw = lastRow To 5 Step -1
With Cells(rw, 2)
myArray = Split(.Value, "/")
If UBound(myArray) > 0 Then
Cells(rw, 2).Value = Application.Trim(myArray(UBound(myArray)))
For i = 0 To UBound(myArray) - 1
Rows(rw).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows(rw + 1).Copy Rows(rw)
Cells(rw, 2).Value = Application.Trim(myArray(i))
Next i
End If
End With
Next rw
End Sub
[/vba] Assumes list starts at row 5

[av8tordude]

AWESOME!!! Thank you p45cal