Help with text

G

G3£wh\)zZ@

Cant seem to think today for some reason....

anyway...

Somehow ive damaged the cd that contains my chart database and i cant find
my back up...

However i have found my text files..so here goes..

I have over 60 text files that contains chart info like this..

Eddy Grant
Can't Get Enough Of You
4 Jan
13

David Bowie
Scary Monsters (& Super Creeps)
10 Jan
20

And so on...I am trying to import them into Excel and have them so theyre
like this
but with the artist etc in one column and the song in another and the date
in another and the position in another..

Eddy Grant Can't Get Enough Of You 4 Jan 13
David Bowie Scary Monsters (& Super Creeps) 10 Jan 20
James Brown Rapp Payback (Where Iz Moses?) 10 Jan 39

I have done this in the past using the same text lists but for some reason i
can`t seem to remember what i did..
 
G

Gord Dibben

If all the data is as your example, i.e. 4 rows and a blank row, the code
below will move each to 4 columns per row.

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error GoTo endit
nocols = InputBox("Enter Number of Columns Desired")
If nocols = "" Or Not IsNumeric(nocols) Then Exit Sub
For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents
Exit Sub
endit:
End Sub

Copy/paste the code to a general module in your workbook. Hit ALT + F11
to get to the Visual Basic Editor. View>Project Explorer. Left-click on your
workbook/project. Insert>Module. Paste in here.

ALT + F11 to go back to Excel. Tools>Macro>Macros. Select the macro by name
then Run.

NOTE: When asked "how many columns" enter 5 to account for the blank rows.

Remember also. There is no "Undo" from a macro. Make sure you try this on a
copy of the worksheet first.

Gord Dibben Excel MVP
 
G

G3£wh\)zZ@

Thanks m8... i actually managed to work it out about 30 minutes later..and
tried to cancel the post, however your way seems to be a better way .

Thanks
 
Top