Lists itno Columns/Rows

[edmana]

Hey!

I have a text file with the following type of data:

Joe Smith
Joe's Company
123 Main Street
Anywhere, NY 12345
(212) 555-1212
joe@joesco.com

http://www.joes.com

This repeats over and over again for each contact. I would like to bring this into Excel so that I have a row for each contact and a column for each of the above, such as:
NAME|Company|Address|Address2|Phone|E-mail|Web

I tried doing a cut|paste-special transpose but that put everything in a single row.

Any ideas?

Thanks!
Ed

 

[SkipVought]

Ed,

Not a ideal situation, as you, no doubt, have determined.

In order to "automate" a solution, you need to know EXACTLY what you are working with.

Are there ALWAYS the same number of lines per address?

Is tha last line of the address ALWAYS a domain? And so on for each line of the ADDRESSES in the file.

BTW, where did you get this file? It is an important question, as there is a remote possibility that there may be a way to get the data in some other structure.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[edmana]

This list comes from a website that is generated each time.

Each group looks like the above. One thought I had was to find where each line repeats (like every 8 lines) and then have a formula in another sheet grab Sheet1!A1, Sheet1!A9, Sheet1!A17, etc.

However, I don't know of a way to dynamically build the cell #.

 

[SkipVought]

Assuming that the list starts in A1, and the last line of the first address block is row 7, in B7 enter this formula and copy across to column H
[tt]
=IF(ISBLANK($A8),INDEX($A1:$A7,COLUMN()-1,1),"")
[/tt]
then copy these 7 cells DOWN thru ALL rows of data in your import list.

Then COPY columns B:H

Then right-click in the selection Edit > Paste Special -- VALUES

VOLA, y'all!


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PeterMoran]

Hi,

This is not as smart as Skip's solution, however you may find it worh a look.

Read the help sheet and follow the details.

Good Luck!

Peter Moran