copy or cut every other line then delete the line

[cgreen]

I have a text file that I want to convert to 2007 excel, there is over 65000 rows of data. The problem is the data needed is showing on two rows verses one row and I need everything on one row then delete the extra row.

Example:
A1 = |015031701 ENGINE,AIRCRAFT,TUR A A |
A 2 = | 453,829,327.30 1,518.000 EA 14.82 14.82 |

Outcome I would like:
A1 = |015031701 ENGINE,AIRCRAFT,TUR A A |

B2 = | 453,829,327.30 1,518.000 EA 14.82 14.82 |

Or even better to save time with doing the text to columns .. I would like each item in its own cell:
A1 = 015031701
B1 = ENGINE,AIRCRAFT,TUR
C1 = A
D1 = A
E1 = 453,829,327.30
F1 = 1,518.000
G1 = EA
H1 = 14.82
J1 = 14.82


Please help!!

 

[cgreen]

I have a text file that I want to convert to 2007 excel, there is over 65000 rows of data. The problem is the data needed is showing on two rows verses one row and I need everything on one row then delete the extra row.

Example:
A1 = |015031701 ENGINE,AIRCRAFT,TUR A A |
A 2 = | 453,829,327.30 1,518.000 EA 14.82 14.82 |

Outcome I would like:
A1 = |015031701 ENGINE,AIRCRAFT,TUR A A |

B1 = | 453,829,327.30 1,518.000 EA 14.82 14.82 |

Or even better to save time with doing the text to columns .. I would like each item in its own cell:
A1 = 015031701
B1 = ENGINE,AIRCRAFT,TUR
C1 = A
D1 = A
E1 = 453,829,327.30
F1 = 1,518.000
G1 = EA
H1 = 14.82
J1 = 14.82

** note: corrected B2 to B1 on "outcome I would like" ... sorry for the confusion

 

[cgreen]

Wanted to share the code that a wonderful friend wrote .... it worked great!


Sub Clean_Data()


Dim LastRow As Long
Dim i As Long

LastRow = Cells.SpecialCells(xlLastCell).Row

For i = LastRow To 1 Step -1
y = Range("A" & i).Value

If Left(y, 2) = "|-" Then
Rows(i - 6 & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 6
End If

If Left(y, 2) = "| " Then
Range("B" & i - 1).Value = Range("A" & i - 1) & Range("A" & i)
Range("B" & i).Value = "Delete"
End If

Next i

Columns(1).Delete


End Sub

Sub Formula_Driven()
Dim LastRow As Long
Dim i As Long

LastRow = Cells.SpecialCells(xlLastCell).Row
Range("B1").Select
ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[-1],5)=""Plant"",1,0)"

Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEFT(RC[-1],5)=""Plant"",1,IF(R[-1]C<8,R[-1]C+1,""8""))"
Range("B2").Select
Selection.Copy

Range("B3").Select
Range("B3:B" & LastRow - 1).Select
ActiveSheet.Paste

Range("C1").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]<8,"""",IF(LEFT(RC[-2],2)<>""| "",RC[-2]&R[1]C[-2],""""))"
Range("C1").Select
Selection.Copy
Range("C2").Select

Range("C2:C" & LastRow - 1).Select
ActiveSheet.Paste

Range("C1:C" & LastRow - 1).Select
Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:B").Delete



Range("A1").Select
ActiveCell.FormulaR1C1 = "Filter"

Selection.AutoFilter

ActiveWorkbook.Worksheets(1).AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets(1).AutoFilter.Sort.SortFields.Add Key:=Range _
("A1:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets(1).AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter
With ActiveSheet
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
.Range("$A$1:$A" & LastRow).AutoFilter Field:=1, Criteria1:="="
.Range("$A$1:$A" & LastRow).CurrentRegion.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
Rows(1).Delete

Range("A1:A" & LastRow).Select
Selection.Replace What:="|", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("A:A").Select
Range("A" & LastRow).Activate

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(41, 1), Array(85, 1), Array(115, 1), Array(147, 1) _
, Array(168, 1), Array(173, 1), Array(179, 1)), TrailingMinusNumbers:=True
Cells.Select
'Range("A" & LastRow).Activate
Cells.EntireColumn.AutoFit


End Sub