I lose my number formatting in the mail merge?

P

Perkgolf

Hi,
I'm losing my number formatting when I merge my Excel sheet into my Word
doc? Any ideas?

Thanks
Bruce
 
N

Nick Hodge

Bruce

You will it doesn't travel by any of the transport methods for mailmerge
(DDE or ODBC)

You will need to transfer the data to text. I use a helper column and the
TEXT function and then kill the formula with paste special+Values

=TEXT(B1,"£#,##0.00")

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/
 
P

Perkgolf

Thanks Nick for the help

Bruce

Nick Hodge said:
Bruce

You will it doesn't travel by any of the transport methods for mailmerge
(DDE or ODBC)

You will need to transfer the data to text. I use a helper column and the
TEXT function and then kill the formula with paste special+Values

=TEXT(B1,"£#,##0.00")

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/
Click to expand...
 
D

disbell

Nick,

I am using Word 2007 and Excel 2007. When I try to use ODBC as the
transport method for the Mail Merge in Word, it still does not bring the
currency formatting over from Excel to Word. I have clicked the Confirm Data
Source Box located under Word Options, select the ODBC connection option,
selected the worksheet that contains the data, and even restarted my
computer. Is there something else that needs to be done in order to make the
currency formating transport from Excel 2007 to the Word 2007 Mail Merge?
 
D

Dave Peterson

I'm not Nick and I don't use Office 2007 enough to help. So this may be a waste
of your time...

Debra Dalgleish posted this:

There's an article on the Microsoft web site that might help you:

Answer Box: Numbers don't merge right in Word
http://office.microsoft.com/en-ca/assistance/HA011164951033.aspx

And if you prefer the old Mail Merge helper, Word MVP Suzanne Barnhill
has instructions here:

http://sbarnhill.mvps.org/WordFAQs/CustomizingWord2002.htm

about half way down the page.

==========
I often cheat instead of racking my brain.

I'll insert another column (probably hidden!) and use:
=text(a2,"00000")
(or whatever format I want)
and use that field in the mailmerge.

(Cheating doesn't bother me anymore <vbg>.)
 
D

disbell

Nick,

I am using Word 2007 and Excel 2007. When I try to use ODBC as the
transport method for the Mail Merge in Word, it still does not bring the
currency formatting over from Excel to Word. I have clicked the Confirm Data
Source Box located under Word Options, select the ODBC connection option,
selected the worksheet that contains the data, and even restarted my
computer. Is there something else that needs to be done in order to make the
currency formating transport from Excel 2007 to the Word 2007 Mail Merge?
 
D

disbell

Dave,

Thank you for your response. I found a way to format using text, but this
might not be my best option since the currency field in the spreadsheet may
need to be used for calculations. Thanks again for your response.
 
D

Dave Peterson

How about storing the value in the cell, then use =text() in another cell to
format the value (into text) the way you want.

Then use mailmerge with that helper field.
 
R

rosie

I had this problem also and found a solution that makes it work the correct
way.

From Word, Click on the Microsoft Office Button, click on Work Option (lower
right hand corner), click advanced, move down to the General area and check
Confirm File Format Conversion on Open. I did this and now it works
perfectly.
 
V

Veronica

Rosie,
That was a great tip! Thank you very much!

There's only one thing that you didn't mentioned and might be useful for
others: When you select your list from excel, a "Confirm Data Source" screen
appears and you HAVE to activate the "Show all" mark and other options will
appear at the "Open Data Source" space. Then you can choose the "Excel
Worksheets via DDE" option.
 
P

Paresh Gupta

I have also tried various methods, but to no avail.
My database is SQL and all the currency fields come with 4 decimals. I have
tried the numeric switch as follows but do not get the desired result

{MERGEFIELD rate_with_meal_plan \# #,0.00}

Please help
 
Top