separating cell values deliminated by a "/"
 

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

separating cell values deliminated by a "/"
 

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

Jim Thomlinson


"Bob" wrote:

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

separating cell values deliminated by a "/"
 

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

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Bob" wrote:

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

separating cell values deliminated by a "/"
 

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
--


Gary


"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

separating cell values deliminated by a "/"
 

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

separating cell values deliminated by a "/"
 

I get "Wrong nuber of arguments or invalid property assignment" error message
on (replace(c.....

"Bernie Deitrick" wrote:

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

separating cell values deliminated by a "/"
 

Bob,

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

HTH,
Bernie
MS Excel MVP


"Bob" wrote in message
...
I get "Wrong nuber of arguments or invalid property assignment" error message
on (replace(c.....

"Bernie Deitrick" wrote:

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

separating cell values deliminated by a "/"
 

Beautiful!!!!

Thank you.


"Bernie Deitrick" wrote:

Bob,

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

HTH,
Bernie
MS Excel MVP


"Bob" wrote in message
...
I get "Wrong nuber of arguments or invalid property assignment" error message
on (replace(c.....

"Bernie Deitrick" wrote:

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