# split cell in excel n put it in two other cells

• February 7th, 2013, 11:55 PM
puchku
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??
• February 8th, 2013, 12:35 AM
DataMiser
Re: split cell in excel n put it in two other cells
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
• February 8th, 2013, 01:09 AM
puchku
Re: split cell in excel n put it in two other cells
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
• February 8th, 2013, 07:10 AM
Eri523
Re: split cell in excel n put it in two other cells
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.