From Text

[MarkC]

I need to import thousands of lines of data
the data comprises of 3 Rows like this


10 10
Elo's Greatest Hits
Electric Light Orchestra

I could do with importing into 4 columns like this

each / represents a column.

10/10/Electric Light Orchestra/Elo's Greatest Hits


Is this possible?


Thanks

 

[Pete_UK]

I've assumed your data is in column A starting in A1. I've also
assumed that you have a single space between the two numbers on the
first row. Put these formulae in the cells stated:

C1: =LEFT(INDEX(A:A,ROW(A1)*3-2),SEARCH("
",INDEX(A:A,ROW(A1)*3-2))-1)

D1: =MID(INDEX(A:A,ROW(A1)*3-2),SEARCH(" ",INDEX(A:A,ROW(A1)*3-2))
+1,255)

E1: =TRIM(INDEX(A:A,ROW(A1)*3))

F1: =TRIM(INDEX(A:A,ROW(A1)*3-1))

Note that the first two formulae return the numbers as text values. If
you want proper numbers then you can just carry out some simple
arithmetic on the formulae, e.g. by adding zero or multiplying by 1
after the final close-bracket.

Then you can copy the formulae down as far as you need to.

If you want to dispense with the original data in column A then you
need to fix the values first. Highlight columns C to F, click <copy>,
then right-click and choose Paste Special | Values | OK then <Esc>.
Then you can delete columns A and B, and maybe use File | Save As to
save the file with a different name so that your original data file
remains unchanged.

Hope this helps.

Pete