separating cell values deliminated by a "/"

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

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 D3>book
A4:D4 with D4>boy
A5:D5 with D5>car
 
You can try Data -> Text to Columns -> Delimited -> / and you should be good
to go...
 
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
 
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
 
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
 
I get "Wrong nuber of arguments or invalid property assignment" error message
 
Bob,

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

HTH,
Bernie
MS Excel MVP
 
Beautiful!!!!

Thank you.


Bernie Deitrick said:
Bob,

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

HTH,
Bernie
MS Excel MVP
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro that analyses 3 columns and put results in 4th 5
Loop by Group Within Group 6
Macro to concatenate columns 1
Recorded macro selection widened 4
using LEN / LEFT / MID / FIND functions to create a list 5
Need macro that analyses 2 columns . Delets row if same text is fo 3
Rows in macros 1
Macro that change words in predefined numbers 9

Back
Top