Inserting commas into cells with mailing addresses

I have spreadsheet that contains about 25,000 rows of client addresses. The columns/data is set up as follows for example

ColumnA...........ColumnB..................ColumnC
Jim Smith..........123 Apple Road.........New York NY 10022-3456
Tina Jone..........456 Apple Road.........San Diego CA 12345-1111
Bob Smith..........789 Peach Lane........Seattle WA 59802-3344
Tim Smith..........4255 Lime Lane.........Missoula MT 59802-3333

I am having an issue with the vendor whom needs to do a mailing of all 25,000 clients. They have indicated that they require comma delimited data to create the mailing. In order to do this, I need to place a comma after the city and a comma after the state.

So as in the first row of data the cells looks like this:

New York NY 10022-3456

The vendor needs it to look like this:

New York, NY, 10022-3456

Is there a function and/or VBA procedure to put commas in where I need them?

Thanks.

Assuming cell C2 holds the first set of data, a function to do this is:

Please Login or Register  to view this content.

This presumes ALL the data has a 10-digit zip code and a two letter state abbreviation.

To be honest, what they really want is a comma-delimited file. I wouldn't do it this way. I would split the City / State / Zip into separate columns and then export the data as a comma-delimited file, which will put the commas in for you as the file is saved.

If you want to do that, just pick three rows to the left, and cut out each of the three sections of code above to get each part:

Please Login or Register  to view this content.

Then copy the range and paste over itself Paste Special > Values. Now you can delete the original grouped set and export the data.

BTW, your sample above included spaces after the added commas. The formula I gave you gives that result. If you really wanted it without the extra spaces:

Please Login or Register  to view this content.

This will look like this:

New York,NY,10022-3456

For the varying zip code, use these formulas:

Please Login or Register  to view this content.