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