Formatting zip codes to semicolon deliminated values

[mattjdohm]

Hi

I'm doing some stuff at my place of work that requires I save large lists of zip codes stored in excel and format them so that they are all on one line and separated by semicolon.

So far I've come up with using the ampersand "&" to join one column of cells to a column of semicolons then 'pasting special' and 'transpose' to have them listed horizonally. That works, time consumingly, and only provides me up to 256 items.

Is there a way I can nest '&' to join many, many cells into the same cell? I've also considered saving as .CSV and 'replace all' to change the commas to semicolons, though the values appear on different lines which is not workable.

Let me know your thoughts. Macros would be fine, too. Thanks so much in advance!

This is Excel2007 also.

[Paul]

Hi Matt,

Maybe this solution will work for you:

Let's say your zip codes are in column A, from A1:A100. In B1 put the formula

=A1

In B2 put the formula

=B1&";"&A2

Fill that formula down to B100. In cell B100 you will have a full list of all the zip codes, separated by semi-colons. If you want to delete B1:B99 you can ---- But first be sure to copy cell B100 and use PasteSpecial --> Values (either in B100 or another cell) so that all your zip codes don't disappear when deleting all the previous formulas!

There are certainly other ways to do this, including VBA, but this is definitely quick and painless.

[mattjdohm]

That's excellent. It's always cool how you can do crazy things simply in excel :D

Let me see what format we generally get zip code lists in and I'll post in this thread if I need any tips formatting them into the right kind of list.

Also, let me know if anyone else has any ideas.

Thanks again!