Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying certain lines from a text file
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying certain lines from a text file
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying certain lines from a text file
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying certain lines from a text file
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting Lines or Copying lines with formulas but without data | Excel Discussion (Misc queries) | |||
text box insert in Excel - text box lines print on second copy | Excel Discussion (Misc queries) | |||
Some lines of text in Excel field do not wrap text. Please help. | Excel Discussion (Misc queries) | |||
Excel, how copy subtotals without copying all lines inbetween? | Excel Discussion (Misc queries) | |||
Copying the Editing in one file to Another opened XLS file | Excel Worksheet Functions |