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

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
Email
Website
Name 2
Email 2
Website 2
Name 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.

marked as duplicate by DavidPostill♦, Scott, fixer1234, karel, StevenOct 17 '15 at 23:46

I would do this in Notepad++.

Paste the whole thing into np++

I understand it will look like this:

``````Name
Email
Website
Name 2
Email 2
Website 2
Name 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
Email 2
Website 2
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.

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:

• Wow - cool! Can you explain the syntax? – Adam Oct 16 '15 at 17:29
• @AdamThe formula creates a pattern of indices to map a single column into a matrix. Any single column can be mapped into a 2-D table by adjusting the formula.................. There are similar formulas to map any 2-D table into a single column as well. – Gary's Student Oct 16 '15 at 17:40