Sheeloo wrote:
I know the feeling - when you find a new and better way of doing something
you had been doing for years... elation and regret (why did I not know this
earlier feeling)

Thanks for the feedback... We love to hear the cases where someone benefited
from our experience...

"maryc" wrote:

This is the ANSWER! I am in CT and we have zips starting with 0. For years
I have been formatting to text, then reenterin zero's. WHAT A WAISTE OF TIME!
Sheeloo has the answer!!!!!! YIPPPIEEEE! You are a life saver!

"Sheeloo" wrote:

I have lived in NJ so I know zip code and start with a zero :-)

What do you mean when you say 'converting it to word via mail merge'?

Suppose you have your zip codes in Col A
then enter this in B1 and copy down
=IF(LEN(A2)=4, "0"&A2,""&A2)

This will prefix a 0 to four digit zipcodes and convert them to Text

Next, select Col B, press Ctrl-C to copy, Right-click on B1, chooshe Paste
Special and then select Values, click OK...

You can now use Col B as your zip codes...



I love lightbulb moments!

For kicks, here is another way for the OP:

=RIGHT("0000" & A2,5)

Just in case you have 1-digit zip codes (^:, or want to pad any "number"
with zeros on the left. Useful for SSNs, flat-file fields, or anywhere
else you need a string to be a specific length.