Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
split text into rows and have the first clumn information repeated
Hi,
I have data like the followings: A B C 1 DrugA Preclinical I 5/3/03 Preclinical II 5/5/05 PC III 5/5/06 2 DrugB Preclinical III 4/3/03 Phase II 9/5/05 PC III 1/5/06 The information under B is seperated by square(alt+0101). How can I get the list like A B C 1 DrugA Preclinical I 5/3/03 2 DrugA Preclinical II 5/5/05 3 DrugA PC III 5/5/06 4 DrugB Preclinical III 4/3/03 5 DrugB Phase II 9/5/05 6 DrugB PC III 1/5/06 Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
split text into rows and have the first clumn information repeated
Public Sub test() Dim iLastRow As Long Dim i As Long Dim iPos As Long Dim iStart As Long Dim iEnd As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = iLastRow To 1 Step -1 Do iPos = InStrRev(Cells(i, "B").Value, Chr(10), Len(Cells(i, "B").Value)) If iPos 0 Then Rows(i + 1).Insert Cells(i + 1, "A").Value = Cells(i, "A").Value Cells(i + 1, "B").Value = Mid(Cells(i, "B").Value, iPos + 1, Len(Cells(i, "B").Value) - iPos) Cells(i, "B").Value = Left(Cells(i, "B").Value, iPos - 1) iPos = InStrRev(Cells(i, "C").Value, Chr(10), Len(Cells(i, "C").Value)) Cells(i + 1, "C").Value = Mid(Cells(i, "C").Value, iPos + 1, Len(Cells(i, "C").Value) - iPos) Cells(i, "C").Value = Left(Cells(i, "C").Value, iPos - 1) End If Loop Until iPos = 0 Next i End With End Sub -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Chia" wrote in message ... Hi, I have data like the followings: A B C 1 DrugA Preclinical I 5/3/03 Preclinical II 5/5/05 PC III 5/5/06 2 DrugB Preclinical III 4/3/03 Phase II 9/5/05 PC III 1/5/06 The information under B is seperated by square(alt+0101). How can I get the list like A B C 1 DrugA Preclinical I 5/3/03 2 DrugA Preclinical II 5/5/05 3 DrugA PC III 5/5/06 4 DrugB Preclinical III 4/3/03 5 DrugB Phase II 9/5/05 6 DrugB PC III 1/5/06 Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
repeated rows to single rows with many columns | Excel Discussion (Misc queries) | |||
How do I split a column of information into two? | New Users to Excel | |||
split text into rows and have the first clumn information repeated | Excel Discussion (Misc queries) | |||
Split text , uniformly paste in rows and columns. | Excel Programming | |||
Printing text in a repeated cell/row that is longer than repeated | Excel Discussion (Misc queries) |