Arrange columns of names & addresses into rows

**SKMaverick** · 05-04-2010, 02:00 AM

I've got a list of names and addresses that I'm trying to organize for a mail merge. These list of names are in a CSV format, I've attached a file as an example. (I had to upload it as a xlsx file...Please change the file extension back to *.csv after you've downloaded it, as the as the forum only allow for excel & picture files). What I am trying to achieve is the final product which looks like the 2nd screenshot. As you can see from the screenshot, there is no consistency where the addresses are. Some are in one cell, some are on 2 cells... to make things worse, the list of names & addresses are in a 3 column format.

What is the best way to achieve this? Is VBA script the most appropriate solution or is there an Excel function that would ado the job?

Original File layout:
\1

Would like the result to look like this:
\1

**teylyn** · 05-04-2010, 02:17 AM

SKMaverick,

it would be a lot easier to advise or provide a solution if you posted the workbook from which you took your screenshots. After all, you want help with Excel, not with pictures, right?

Edit: I see you attached a workbook. Unfortunately, my Excel tells me it's an invalid file format and I can't open it.

**SKMaverick** · 05-04-2010, 10:37 AM

Originally Posted by teylyn

SKMaverick,
Edit: I see you attached a workbook. Unfortunately, my Excel tells me it's an invalid file format and I can't open it.

As I said in my original post, the file name needs to be changed. So after you've downloaded the attachment, change the file name
from: sample.csv.xlsx
to: sample.csv

Afterwards you should be able to open it just fine.

**StephenR** · 05-04-2010, 11:06 AM

I think with five and a half thousand posts teylyn knows how to open a csv file. There is a problem with the attachment.

**SKMaverick** · 05-04-2010, 12:45 PM

Originally Posted by StephenR

I think with five and a half thousand posts teylyn knows how to open a csv file. There is a problem with the attachment.

While teylyn may have over 5000 posts, we are all human and it's possible she overlooked something. There is nothing wrong with the file attachment. I've downloaded it and tested it on two different computers and it opens just fine in CSV format. Either the file extension was not changed or there was a step she/he missed.

**JBeaucaire** · 05-04-2010, 12:53 PM

Try this on data after you've opened it Excel:

Please Login or Register  to view this content.

You can fix the one name that puts JR. alone as last name.

**SKMaverick** · 05-04-2010, 01:27 PM

JBeaucaire,

It works beautifully. Just one small thing however -- what line of code do I need to insert so that the results appear in a new workbook? The reason for this is because I'd like to save the results in a new file *.xlsx format.

**JBeaucaire** · 05-04-2010, 02:36 PM

Just do a SAVE AS afterwards.

=========
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.