Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Empty a cell if the values equal to "IN" , "MC" or "PP" | Excel Programming | |||
Setting values for "TRUE","FALSE" and "#REF!" | Excel Programming | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions |