Converting Excel List into Word

Hi Guys,

I have an Excel list of names that I need sorted into word seperated by commas.

Current format in Excel is as follows:

Joe Bloggs
Joe Bloggs 2
Joe Bloggs 3

There is over 20,000 records like this, however I've been sorting them by copying each individual name, pasting them into word and manually entering a comma between names and it's taking forever! I've only got through 1,000 records and that's taken me the best part of a week.

There must be a way of copying the entire column and pasting it into word with commas between them?

Any help would be much appreciated,

Thanks,

Sean

 

There should be an option when saving to save the file as CSV comma delimited (usually most excel type programs have that). If not, download Open Office, I know it has that option for sure.

 

There must be an easier way?

Tried saving as a CSV Comma delimited, but it doesn't actually insert commas - it just saves the file in a different format but the exact same layout as the original list...

 

Assuming you have names in column a cells a1,a2,a3
Enter cell b2 as =A1&","&A2
Then copy cell b2 formula to b3 etc
Add as many rows as you like

Excel does have a limitation of about 1024 characters per cell (or used to) - but this should reduce the copy and paste effort, and you can easily see the cells it starts to go wrong in.
You will need to copy and paste special - values only to get what you want from the final cells

 

Thanks for that - that's getting me a lot closer, however what's happening when I follow that command is it's inserting a comma after each name for me, but it's still in a list going down (B1, B2, B3 etc)

When I copy and paste that into word, it copies it with the commas, but still in a list format going down - I've tried word wrapping hoping that it would bring the names together, but they stay in a list format, so i'll still have to go down each name and press delete to bring them together.. if that makes sense?

Any idea how to get around that?

Many Thanks.

 

sorry my mistake
change b3 to =b2&","&a3 - hope that works?

 

Thanks for that

 

Hope that got part way there.
If you want a quick program to automate it - I could do that for you if you want to pm me
But probably not worth the effort now?

 

if you do save as CSV and select it to be comma delimited then it will be creating close to what you want. If you open it with word you will see (excel will interpret it and make it look like the original spreadsheet) and you would then just need to remove the line breaks which you may be able to do with a find&replace command but certainly only a couple of lines of code