Parse Data

I have a spreadsheet that lists over 200 business names, addresses, phone numbers, and email addresses. Instead of the data being separated to columns, it is all in one column. Every 12th line starts a new set of data. All the data is in the same order(i.e. Business name, owner, street address, city, state, zip, phone, fax, url, email address, blank line) How can I parse eleven consecutive lines to columns, then the next 11 lines to matching columns, and so on quickly?

chadwellp,

Please attach your workbook.

Click on "New Post" and scroll down and see "Manage Attachments".

1) Put this formula in C1:

=INDEX($A:$A, (ROW($A1)*11)-11+COLUMN(A$1))

2) Copy it down as far as needed and across to column M.

3) Highlight all the new data

4) CTRL-C to copy

5) Edit > Paste Special > Values to remove the formulas

6) Delete columns A:B

A small tweak to the formula:

=INDEX($A:$A, (ROW($A1)*11)-11+COLUMN(A$1))&""

You won't see any zeros this way.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].