Problem formatting ZIP code in mail merge

[Peter Serratore]

Hello all--

I probably just forgot one small step in the process, but
that of course makes the whole thing not work...

I'm trying to format a column of cells with 5-digit ZIP
codes in such a way that they display as 5 full digits IN
A MAIL MERGE instead of lopping off the first zero
("06604" instead of "6604").

I've tried this method several times, but it hasn't
worked:

I select the column;
I go into the "Format" menu
I select "Cells"
I select the "Number" tab
I highlight "Special"
I select "ZIP code" from the pop-up box

The column at that point displays as it should--a ZIP
code that begins with zero displays that first zero.

BUT- Here's the problem: when I try to do a mail merge in
MS Word using that Excel worksheet, the ZIP codes
thatstart with zero, are once again shown on the labels
with the first zero lopped off.

Huh?

Thanks,

Peter Serratore

 

[Paul B]

Peter, try formatting the cells as text before the merge and see if this
will fix it.

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[Guest]

Hi Paul--

Thanks for reply.

I tried that a few minutes ago, but as soon as I
formatted the ZIP code column as text, it immediately
lopped the initial zeroes off the ZIPs--it didn't even
wait for the mail merge to do it!

-Peter

 

[Paul B]

Peter, try this macro from David McRitchie to convert the sheet to text, it
will keep the 00's

Sub AllCellsToText()
'will copy the sheet and convert all to text
'D.McRitchie, posted 2003-01-17 worksheet.functions
'specifically for use with Mail Merge
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
Dim str As String
'-- to change the activesheet comment out the .copy line below
Sheets(ActiveSheet.Name).Copy Before:=Sheets(ActiveSheet.Name)
On Error Resume Next 'In case no such cells in selection
For Each cell In Cells.SpecialCells(xlFormulas)
cell.Value = cell.Value
Next cell
For Each cell In Cells.SpecialCells(xlConstants, xlNumbers)
str = cell.Text
cell.NumberFormat = "@"
cell.Value = str
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[Peter Serratore]

Hi again Paul--

I have NO idea at all what all that is. I know what a
macro is, and have created a few in Word, but I can't
even tell where it begins in your message (and surely
can't decipher the commands)!

-Peter

 

[Dave Peterson]

I've never merged, but I've saved a couple responses from Deb Dalgleish.

She's recommended this to other people with formatting problems between excel
and word.

==============

In Word, after you have inserted the Merge fields, press Alt+F9, to view
the field codes.

In the field code for the Zip Code, you can add a switch to format the
number. For example:
{ MERGEFIELD "Zip" \# "00000" }

Press Alt+F9 again to hide the field codes, then view the merged data.

 

[David McRitchie]

Hi Peter,
I guess if you're a Word person you'd prefer Debra's solution that
Dave Peterson gave you.

As far as installing the macro into
Excel is concerned the macro begins at SUB ....
and ends with END SUB directions to install a macro are in
http://www.mvps.org/dmcritchie/excel/getstarted.htm