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,
--
William

Adding Hyphens to a 9 Digit Zip Code Column in Excel

1. Select the column with the zip codes that you want to add hyphens to.
2. Click on the "Find & Replace" button in the "Editing" section of the "Home" tab.
3. In the "Find and Replace" dialog box, click on the "Replace" tab.
4. In the "Find what" field, enter the last 4 digits of the zip code without a hyphen (e.g. 1234).
5. In the "Replace with" field, enter the same 4 digits with a hyphen before them (e.g. -1234).
6. Click on the "Replace All" button.

This will add a hyphen before the last 4 digits of all the zip codes in the selected column. Now you can use this column in your mail merge without having to manually add the hyphens.

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

so if A1 contained 085401234 the formula gives you 08540-1234
--
Gary''s Student - gsnu200860


"William" wrote:

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,
--
William

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.

"William" wrote:

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,
--
William

FormatCellsNumberSpecial Zip + 4


Gord Dibben MS Excel MVP

On Thu, 13 Aug 2009 05:50:01 -0700, William
wrote:

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,

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.
--
HTH...

Jim Thomlinson


"James" wrote:

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.

"William" wrote:

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,
--
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?
--
William


"Gord Dibben" wrote:

FormatCellsNumberSpecial Zip + 4


Gord Dibben MS Excel MVP

On Thu, 13 Aug 2009 05:50:01 -0700, William
wrote:

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,

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(A199999, text(a1, "00000-0000"), text(a1, "00000"))
--
HTH...

Jim Thomlinson


"William" wrote:

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?
--
William


"Gord Dibben" wrote:

FormatCellsNumberSpecial Zip + 4


Gord Dibben MS Excel MVP

On Thu, 13 Aug 2009 05:50:01 -0700, William
wrote:

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,

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

"William" wrote in message
...
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?
--
William


"Gord Dibben" wrote:

FormatCellsNumberSpecial Zip + 4


Gord Dibben MS Excel MVP

On Thu, 13 Aug 2009 05:50:01 -0700, William
wrote:

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,