Move 1st Row out of 9 to Column A, Move 2nd Row of 9 to Column B, etc.

Hi All,

I imported some contact data from a PDF and now all the data is in one column. The order of the data is
row 1: Name
row 2: Title
row 3: Address 1
row 4: Address 2
row 5: P: Phone
row 6: F: fax
Row 7: E: Email
Row 8: www. Website

and it repeats for the following entries, with no spaces. I added the P, F, E and www. because these labels are in front of each phone, fax, email and website entry, giving us a constant in the data.

I am trying to create a rule where the 1st row out of every 8 moves to column A, the 2nd row out of every 8 moves to column B, etc. This way all names will appear in one column, titles in another, etc.

How can I create a rule for Excel to move the data in this fashion?

Also, the data unfortunately has some inconsistencies, because it is also sorted by state, so certain entries also have State above the contact information entry, throwing off the order of the data. Is there a way to factor in this variable as well? The state, as I indicated, is underlined, which could form the basis for a possible exception to a rule.

Thanks in advance for any help.

Take a look at this post from yesterday:

http://www.excelforum.com/excel-gene...o-columns.html

Hope this helps.

Pete

Great, thank you!I really appreciate it!

I have one more question. On some of the lines, the title of the person is in the same line as their name, and I want to create a rule that will move their title to a new row. All of the people's titles start with "Superintendent" followed by the name of the school, i.e. "Jane Doe Superintendent - School Name." Is there a way to create a rule where every time there is the word 'Superintendent' on the same line as the person's name, it moves the word 'Superintendent' and everything else that follows it in the cell to the row below?

I've attached a short example. In the actual spreadsheet, some of the names in the list already have the title in the appropriate place, a row below the person's name. Some in the list have names and titles combined.

Thanks for your help!

Thanks for your reply! I decided to remove the websites and edited the formula you posted above to account for the change.