Excel text to columns question

I am making a mailing list in Excel to send letters advertising my new business. I copied and pasted data from Yahoo Business Listings onto an Excel Spreadsheet.

The problem is this, I now have several hundreds of lines of data and I need to move certain data over to the correct columns.

A simple text to columns doesn't work here because data is not formatted to do it quickly.

Example:
A1 - Business Name B1 - Phone C1 - Address D1 - City
A2 - ABC Company
A3 - (702) 555-3100 1200 S Main St
A4 - Houston
A2 and every 3rd row down contains the Business Name
A3 and every 3rd row down contains both the phone number and address
A4 and every 3rd row down contains the city
I don't need to move A2
I need to move A3 to B3 and C3
I need to move A4 to D4

Then I can run a macro to eliminate the empty cells, I already have the code.

My question - is there a simple way to move this data or do I need to go in and format it so I can use text to columns?

Any help is appreciated.

Thanks!

Text to columns is somewhat flexible... what are you using to delineate (comma, space, tab)?

A little trick I used to use is to place the text into Notepad, change it to a fixed-width font, copy and past into Wordpad and use Find & replace (if needed) to get a unique separator to use in Excel.

Of course, this doesn't always work, but it came in handy for me.

Thanks for the comment... but I've tried several different ways to use text to columns and it only helps a little, only on the address and phone number lines. It separates the two but it leaves open spaces before the address and I have to go back and remove them. I've been manually fixing the data into the different columns. It's a very long process and it will take days to get it finished.

One thing that might help is getting all the address data on one line. Using your example, if you selected all the data relating to a single address record and select Copy--> Paste Special --> Transpose (check the transpose checkbox); it moves all the data to one line. Of course, you would likely have to do this manually for each record (unless you could devise a macro that can find the blank cell between records and select the rows above until it finds another blank).

Once the record is transposed, it is much easier (though still imperfect) for text to columns.

Open spaces as in extra columns?

Usually, the hard part is getting a separator for every section in the source before importing the text to Excel. Sometimes it's easier to edit the content of the source before exporting it as text.

Unfortunately, sometimes you end up spending time on every entry.