Bob,

This macro will process as many values in column D as you have.... version 1 overwrites the original
values with the / and version 2 leaves them in.

HTH,
Bernie
MS Excel MVP

Sub Version1()
Dim c As Range
Dim myC As Integer
Dim myR As Long
Dim i As Long

myR = Cells(Rows.Count, 4).End(xlUp).Row

For i = myR To 2 Step -1
Set c = Cells(i, 4)
If InStr(1, c.Value, "/") 0 Then
myC = Len(c.Value) - Len(Replace(c.Value, "/", ""))
c.EntireRow.Copy
c.Offset(1).EntireRow.Resize(myC).Insert
c.Resize(myC + 1, 1).Value = Application.Transpose(Split(c.Value, "/"))
End If
Next i

End Sub


Sub Version2()
Dim c As Range
Dim myC As Integer
Dim myR As Long
Dim i As Long

myR = Cells(Rows.Count, 4).End(xlUp).Row

For i = myR To 2 Step -1
Set c = Cells(i, 4)
If InStr(1, c.Value, "/") 0 Then
myC = Len(c.Value) - Len(Replace(c.Value, "/", ""))
c.EntireRow.Copy
c.Offset(1).EntireRow.Resize(myC + 1).Insert
c.Offset(1, 0).Resize(myC + 1, 1).Value = Application.Transpose(Split(c.Value, "/"))
End If
Next i

End Sub




"Bob" wrote in message
...
I have a data in a A2:D2. The data in D2 is separated by / (book/boy/car).

I would like to separate the data into as many rows as there are "/".

the result would be A3:C3 repeated and D3book
A4:D4 with D4boy
A5:D5 with D5car