How to import this data in two columns of an excel file ?

[Adriano]

I have a txt file like this:
.. Chapter 1
[1][2]
[3][4][5]

[27]

[28]


[29]
.. Chapter 2
[1]

[18]
.. Chapter 3
[1]

[14]
[20]

There's a way to import it in an excel file and have in first coulumn for
each row a chapter and in the next column all the rest of the text like
that:
COLUMN 1 COLUMN 2
1 ROW Chapter 1 [1][2] [3][4][5] [27] [28] [29]
2 ROW Chapter 2 [1] [18]
3 ROW Chapter 3 [1] [14][20]

Thanks
Adriano

 

[Dave Peterson]

I think I'd just bring it into column A and then separate it into columns.

Option Explicit
Sub testme01()

Dim curWks As Worksheet
Dim newWks As Worksheet

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long

Set curWks = Worksheets("sheet1")
With curWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

If LCase(.Cells(FirstRow, "A").Value) Like "*chapter*" Then
'ok to continue
Else
MsgBox "Please start with row that has Chapter in it!"
Exit Sub
End If

Set newWks = Worksheets.Add

oRow = 0
For iRow = FirstRow To LastRow
If InStr(1, .Cells(iRow, "A"), "chapter", vbTextCompare) > 0 Then
oRow = oRow + 1
newWks.Cells(oRow, "A").Value = Mid(.Cells(iRow, "A").Value, 3)
Else
If Trim(.Cells(iRow, "a").Value) = "" Then
newWks.Cells(oRow, "B").Value _
= newWks.Cells(oRow, "B").Value & " "
Else
newWks.Cells(oRow, "B").Value _
= newWks.Cells(oRow, "B") & .Cells(iRow, "A").Value
End If
End If
Next iRow
End With

newWks.UsedRange.Columns.AutoFit

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

I have a txt file like this:
. Chapter 1
[1][2]
[3][4][5]

[27]

[28]

[29]
. Chapter 2
[1]

[18]
. Chapter 3
[1]

[14]
[20]

There's a way to import it in an excel file and have in first coulumn for
each row a chapter and in the next column all the rest of the text like
that:
COLUMN 1 COLUMN 2
1 ROW Chapter 1 [1][2] [3][4][5] [27] [28] [29]
2 ROW Chapter 2 [1] [18]
3 ROW Chapter 3 [1] [14][20]

Thanks
Adriano

 

[Adriano]

Dave,
i've an error message here:

runtime error 1004:

when debug i find this line highlight :

newWks.Cells(oRow, "B").Value _
= newWks.Cells(oRow, "B") & .Cells(iRow, "A").Value

(I use italian excel 2003)
What do you think ?
Thanks for your help
Adriano

 

[Adriano]

Dave,
i've solved ... it was my mistake ... it works perfectly now
i have only one problem

the columns are ok but the data (text) of the second column is always cutted
on 255 characters (the macro pass only the first 255 characters and
automagically erase the rest of the text) ... why ?


thank you very much
Adriano

 

[Adriano]

Me again ...
The 255 character problem is caused from conversion of excel 2003 file into
4.0 excel .. i need to convert the sheet from excel 2003 into 4.0 ... and in
this conversion all the text above 255 characters is cutted ... why ?

Adriano

 

[Dave Peterson]

Early versions of excel couldn't support more than 255 characters per cell.

xl97 was the first first that allowed up to 32k characters.