separating cell values deliminated by a "/"

[Bob]

I have a data in a A22. 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 D3>book
A44 with D4>boy
A55 with D5>car

 

[Jim Thomlinson]

You can try Data -> Text to Columns -> Delimited -> / and you should be good
to go...

 

[Bob]

Except that places the data in different columns.
I need the data in different rows:
A2 B2 C2 D2
I see a book/boy/car becomes:

A2 B2 c2 D2
I see a book

A3 B3 C3 D3
I see a boy

A4 B4 C4 D4
I see a car

 

[Gary Keramidas]

i'm not sure what you want because i don't know how many rows of data there are
and what's in columnc A:C


maybe this will be a start

Option Explicit
Sub test()
Dim ws As Worksheet
Dim txt As Variant
Dim i As Long
Set ws = Worksheets("sheet1")
With ws.Range("D2")
txt = Split(.Value, "/")
For i = LBound(txt) To UBound(txt)
.Offset(i + 1).Value = txt(i)
Next
End With
End Sub

 

[Bernie Deitrick]

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]

I get "Wrong nuber of arguments or invalid property assignment" error message

 

[Bernie Deitrick]

Bob,

Are you running Excel97? If so, change Replace to Application.Substitute

HTH,
Bernie
MS Excel MVP

 

[Bob]

Beautiful!!!!

Thank you.

Bob,

Are you running Excel97? If so, change Replace to Application.Substitute

HTH,
Bernie
MS Excel MVP