Displaying Zipcodes in a Word Mailmerge
Excel provides a Special number format for zipcodes which allows those of us in New England to display our zipcodes properly. However, when you do a mail merge in Word 2002 and 2003, this formatting will not come through. A zipcode of 01040 will display as 1040. You can fix this in Excel by converting the number back to text, but if your list is extensive, you won’t want to do this. This tip will show you how to display 5-digit zipcodes properly in a Word mail merge without having to reformat your data.
After you create the merge document and attach your Excel workbook containing the addresses:
1. Insert the merge field for your zip code where you want it.
2. Press ALT+F9 to display the field codes. The Zip code field will look something like this (the text in quotes will be the name of your zip code field). Ignore any shading that might appear.
{ MERGEFIELD “Zip_code” }
3. Change the field to include a number format, as shown below. Make sure to place it inside the curly braces and include the spaces, etc. Make sure to use a backslash \ and not a forward slash /. Note that the number format is enclosed in quotes (“):
{ MERGEFIELD “Zip_code” \# “00000″ }
4. Press ALT+F9 again and preview the merge.
5. You may have to update the field by selecting it and pressing F9 in order to see the results of your change.
[...] Displaying Zip Codes in a Word Mail Merge [...]
[...] Excel Help Don’t send anything to the NE United States?
Try the suggestion here http://blog.ptraining.com/2005/08/di…ord-mailmerge/ — HTH, Barb Reinhardt "leisa.p" wrote: > I am trying to do a mail merge from my [...]
I am trying to figure out how many widgets I need to order for every item listed. I have a sheet of items (rows) where for each row there may be a quantity needed or not. For each row that has an amount needed, I need to create another sheet with ONLY the rows where there is an actual need with the quantity needed, item name and state. Here is sample data of the listing in Worksheet A:
Name State Qty
Apples WA 5
Oranges FL
Car MI 8
Furniture VA 3
Corn IN
Notice that the Qty is empty in many of the rows. Desired output on Worksheet B:
Name State Qty
Apples WA 5
Car MI 8
Furniture VA 3
So the output does not include the empty quantity records. I’ve tried various vlookup formulas and others and am just having a hard time. I’d prefer to do this with a formula rather than macro or such. Any help would be appreciated.
Well, if you want only the rows with numbers, you need a macro in order to skip the unneeded rows. If you are willing to write the formula, then copy/paste values, sort to move the blank rows to the bottom, you can get away with a formula. Or rather 3 formulas.
Assume the data is in Sheet 1 in cols A, B, C, starting on row 2, with column titles in row 1 on both sheets.
In sheet 2, write the following formulas:
A2: =If(C2=”",”",Sheet1!A2)
B2: =If(C2=”",”",Sheet1!B2)
C2: =if(isnumber(Sheet1!C2),Sheet1!C2,”")
Copy the 3 formulas down as far as you need.
Select the formulas, copy them, then Paste Special/Values. This will convert the formulas to values. Sort the range by Col C and the cells with numbers will sort to the top.