|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 15th, 2003, 04:40 PM
|
|||
|
|||
Problem 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 
|
|
#2
October 15th, 2003, 06:18 PM
|
|||
|
|||
|
Problem formatting ZIP code in mail merge
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 ** "Peter Serratore" wrote in message ... 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
|
|
#3
October 15th, 2003, 08:15 PM
|
|||
|
|||
|
Problem formatting ZIP code in mail merge
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 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 ** "Peter Serratore" wrote in message ... 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 .
|
|
#4
October 15th, 2003, 08:25 PM
|
|||
|
|||
|
Problem formatting ZIP code in mail merge
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 Befo=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 ** wrote in message ... 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 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 ** "Peter Serratore" wrote in message ... 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 .
|
|
#5
October 15th, 2003, 09:29 PM
|
|||
|
|||
|
Problem formatting ZIP code in mail merge
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 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 Befo=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 ** wrote in message ... 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 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 ** "Peter Serratore" wrote in message ... 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 . .
|
|
#6
October 16th, 2003, 12:57 AM
|
|||
|
|||
|
Problem formatting ZIP code in mail merge
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. Peter Serratore wrote: 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 -- Dave Peterson
|
|
#7
October 18th, 2003, 01:50 AM
|
|||
|
|||
|
Problem formatting ZIP code in mail merge
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 "Peter Serratore" wrote in message ... 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 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 Befo=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 ** wrote in message ... 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 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 ** "Peter Serratore" wrote in message ... 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 . .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
