split cell in excel n put it in two other cells

i hav a problem.......i have an excel sheet which hav the B column values as
B1=ant & boy
B2=cat & doll etc.....
i need to split these values and paste them in C & D columns in same row.....like i will have
C1= ant, D1=boy
C2=cat, D2=doll......ho wto achieve this??it may b simple.....but am having problems......any help??

I don't work in Excel so I can't help much but I can give you a hint.
Have a look at the Split() function

i hav used split...n here is my code......

Code:

---

Dim xlapp As New Excel.Application
Dim xlbook As New Excel.Workbook
Dim xlsheet As New Excel.Worksheet
Dim text As String
Dim i As Integer
Dim j As Integer
Dim lastnonempty As Integer



Private Sub Form_load()
excel_value
End Sub

Public Sub excel_value()
rowcnt = 2
xlapp.workbooks.open ("D:\Baishali\try.xlsx")
For i = 2 To 5
text = xlsheet.Cells(i, 2)
colcnt = 3
cval = Split(text, "&")
lastnonempty = -1
For j = 0 To UBound(cval)
If cval(j) <> "" Then
lastnonempty = lastnonempty + 1
cval(lastnonempty) = cval(j)
End If
texta = cval(j)
xlsheet.Cells(rowcnt, colcnt) = texta
colcnt = colcnt + 1
Next
rowcnt = rowcnt + 1
Next
End Sub

---

anything wrong?am getting error 430 on red line

xlsheet is uninitialized; how should it know you mean to refer to a sheet (presumably the active one) in the workbook you just opened? Try this instead:

Code:

---

text = xlapp.ActiveSheet.Cells(i, 2)

---

Of course you'll need to fix other relatedreferences later in the code as well.

Or add this line before you make any reference to xlsheet:

Code:

---

Set xlsheet = xlapp.ActiveSheet

---

That way you don't need to modify your references to xlsheet, unless they also contain other bugs.