Hi, I have a text file with 30,000 lines. I need to figure out a way to copy
only the lines that start with [SC] and the surrounding lines (1 above, 1
below) to my excel sheet. I can import the whole sheet into excel, but I cant
figure out how to sort it and still keep the 3 lines I need together.. Any
help pls? Thank you!

How about importing the whole file into a new worksheet in a different workbook.

Then select column A
Edit|Find
[SC}
Then come up two rows
then Shift-ctrl-home
to select that cell through A1
Edit|Delete|entire row

Then select column A again
With A1 the activecell
Edit|find
[SC]
But shift-click-Find Next
This will start at the bottom and work it's way up to find the last [SC] in the
column.

Come down a couple of cells
select that cell
Shift-Ctrl-End to select that cell through the last used cell

Edit|Delete|entire row

And copy what's left to its proper home.


Michael A wrote:

Hi, I have a text file with 30,000 lines. I need to figure out a way to copy
only the lines that start with [SC] and the surrounding lines (1 above, 1
below) to my excel sheet. I can import the whole sheet into excel, but I cant
figure out how to sort it and still keep the 3 lines I need together.. Any
help pls? Thank you!

--

Dave Peterson

We would try

Add help column;
Go down whole list row by row;
If Like "SC*" then dirty 3 rows in help column accordingly
Go up help column;
Delete rows with no entry;
Delete help column

--
Regards

"Michael A" wrote in message
...
Hi, I have a text file with 30,000 lines. I need to figure out a way to
copy
only the lines that start with [SC] and the surrounding lines (1 above, 1
below) to my excel sheet. I can import the whole sheet into excel, but I
cant
figure out how to sort it and still keep the 3 lines I need together.. Any
help pls? Thank you!

Below is a visual basic Macro that will do the trick. Make sure the number
of lines inthe file is a multiuple of 3. If not an errror will occur but you
will stillget results.


Sub read_test()
Const ForReading = 1, ForWriting = 2, ForAppending = 3


fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
Dim fs, f

Set fs = CreateObject("Scripting.FileSystemObject")

Set f = fs.OpenTextFile(fileToOpen, ForReading)

RowCount = 1

Do While f.AtEndOfStream < True

line1 = f.readline
line2 = f.readline
line3 = f.readline

If Left(line2, 4) = "[SC]" Then
Cells(RowCount, "A").Value = line1
RowCount = RowCount + 1
Cells(RowCount, "A").Value = line2
RowCount = RowCount + 1
Cells(RowCount, "A").Value = line3
RowCount = RowCount + 1
End If

Loop

End Sub


"Michael A" wrote:

Hi, I have a text file with 30,000 lines. I need to figure out a way to copy
only the lines that start with [SC] and the surrounding lines (1 above, 1
below) to my excel sheet. I can import the whole sheet into excel, but I cant
figure out how to sort it and still keep the 3 lines I need together.. Any
help pls? Thank you!