 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Setup / October 2003Problem formatting ZIP code in mail merge
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 Peter, try formatting the cells as text before the merge and see if this will fix it.  SignaturePaul 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 ** > Hello all-- > [quoted text clipped - 28 lines] > > Peter Serratore 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 >-----Original Message----- >Peter, try formatting the cells as text before the merge and see if this [quoted text clipped - 34 lines] > >. 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 SignaturePaul 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 ** > Hi Paul-- > [quoted text clipped - 52 lines] > > > >. 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 >-----Original Message----- >Peter, try this macro from David McRitchie to convert the sheet to text, it [quoted text clipped - 81 lines] > >. 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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm > Hi again Paul-- > [quoted text clipped - 109 lines] > > > >. 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. > Hello all-- > [quoted text clipped - 28 lines] > > Peter Serratore SignatureDave Peterson ec35720@msn.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.