Convert long list of data to colums in Excel [duplicate]

Question

I have a pdf that i need converted to an excel spreadsheet. I have been able to get the raw data from it and import it into excel, but i am having trouble importing the data how i need it.

My excel sheet now looks like

Name 
Address 
Email 
Website 
Name 2 
Address 2  
Email 2  
Website 2 
Name 3 
Address 3 
Email 3 
Website 3 
etc.

I need it to look like

Name    Address    Email    Website
Name 2  Address 2  Email 2  Website 2
Name 3  Address 3  Email 3  Website 3

I have tried to add a comma to the end of every line in word and then import, but it didn't work.

Any help is much appreciated! The list is VERY long.

Answer 1

With data in column A, pick any cell not in column A and enter:

=INDEX($A:$A,(4*ROWS($1:1)-4)+(COLUMNS($A:A)),1)

Copy this cell both across and down:

Answer 2

I would do this in Notepad++.

Paste the whole thing into np++

I understand it will look like this:

Name 
Address 
Email 
Website 
Name 2 
Address 2  
Email 2  
Website 2 
Name 3 
Address 3 
Email 3 
Website 3 
etc.

Now do a find replace to replace \r\nAddress with \t Address.

Note that "Extended" search mode is selected in the lower left.

\r\n is the standard new line notation in windows. \t is a tab.

Your data should now look like this:

Name   Address 
Email 
Website 
Name 2   Address 2  
Email 2  
Website 2 
Name 3    Address 3 
Email 3 
Website 3 
etc.

Repeat the find replace to strip the new lines and put in commas in front of email and Website too. Now you have a tab separated file that you can paste into Excel.