Adding a HYPHEN to a 9 digit zip code column

[William]

I have a column with nine-digit zip codes, but there is no hyphen separating
the final zip four numbers. I need to use this column in a mail merge, but
don't want to go in and manually add all the hypens. There must be a way to
do this (Find/Replace using placeholders?) but I can't quite come up with it.
Ideas?

Thanks,

 

[Gary''s Student]

=LEFT(A1,5) & "-" & RIGHT(A1,4)

so if A1 contained 085401234 the formula gives you 08540-1234

 

[James]

One way you can do it is to use the Format > Cells and under the category
select "Custom" in the box enter this 00000-0000. That will seperate the the
last 4 digits with a dash.

 

[Gord Dibben]

Format>Cells>Number>Special Zip + 4


Gord Dibben MS Excel MVP

 

[Jim Thomlinson]

2 problems with that...

1. If the postal code is only 5 digits then format returns
00001-2345
You are better off with this custom format
[>99999]0000-0000; 00000

2. Formats will not work in this instance. The OP is doing a mail merge and
formats are not carried forward as part of the merge.

 

[William]

Thanks for the ideas. It seems as there is no solution yet. The formatting
suggestions do produce 00001-0000 when there are only 5 digits in a cell.
And Jim's custom format solves that, but cannot be used in a mail merge.

I wonder if there is a way to use Jim's custom formula, and then find a way
for the format to come through the merge?

 

[Jim Thomlinson]

Here is an article on mail merging to XL. You can switch your merge to DDE to
get the formats to come through but there are some things about DDE that you
should read up on first. I would go with Gary''s solution or some version as
such. It converts the zip code to text and that will come through just fine...

http://tips.pjmsn.me.uk/t0003.htm

I would use this formula...
=if(A1>99999, text(a1, "00000-0000"), text(a1, "00000"))

 

[David Biddulph]

If you want to use the output of that custom format, use
=TEXT(A1,"00000-0000")