[This followup was posted to microsoft.public.excel.worksheet.functions
with an email copy to Lynn.
Please use the newsgroup for further discussion.]
I'm not sure why you are having a problem. I just tested a merge and
it worked as expected.
Remember that in XL formatting applies only to numeric data, not text
data (even when the text looks like numbers). Also, formatting only
changes the way XL *shows* the data. It doesn't change the data
itself. So, if you have a number such as 123456789 and choose to
format it as Zip+4, XL will show it as 12345-6789 but it is still the
number 123456789. Similarly, changing the format to $#,##0 will simply
change the *appearance* to $123,456,789
When Word carries out a mail merge it apparently ignores XL's format
setting. So, the number 123456789 will come through as just that --
*irrespective* of how it is formatted in XL.
In my test, I created a cell with the number above and formatted with
Zip+4. In another column I entered 12345-6789. Since this is not a
legitimate number, XL will treat is as a text field. [Note that
applying any kind of number format to this latter field is an exercise
in futility since XL will not apply a number format to a text field!]
In Word, I used Tools | Letters and Mailings > Mail Merge Wizard...
specified the XL workbook as the source, and stepped through the rest
of the wizard. Word showed the first field as 123456789 (even though
in XL its format was Zip+4) and the 2nd field as 12345-6789.
Neither showed up as zero. That's the confusing part.
--
Trouble finding replies to your posts? Use a newsreader. See the
tutorial 'Outlook Express and Newsgroups' on my web site
Regards,
Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
