excel - merge cells including a zip code

Question

I have the need to merge a bunch of cells that comprise an address (street, city, state, zip) into a single cell. No problem except with the zip code.

The zip cell has only 4 digits for any zip that starts with 0. So, I change it's format to be Special -> Zip Code. That makes the cell itself show the beginning 0, but the merged cell still does not show the leading 0.

Does anyone know how to get the leading 0 in the merged column?

Thanks

Evan

Answer 1

If you have the Zip code in cell A1 you can use the formula =TEXT(A1, "00000") to format the Zip code as 5 digits wide with leading zeros and then merge that instead of the numeric version.

Answer 2

See this question

http://superuser.com/questions/150908/ms-word-2007-mail-merge-fails-on-zip-codes-with-leading-zeros-eg-01234/151004#151004