1

This question already has an answer here:

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.

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

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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.

Before Find/Replace 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.

After Find/Replace 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.

2

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:

enter image description here

  • 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

Not the answer you're looking for? Browse other questions tagged or ask your own question.