[mso] Re: Excel and Word mailmerge - number formatting import problems
- From: "Ray Blake" <ray@xxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Thu, 21 Aug 2003 17:51:26 +0100
Can't help on WHY this is happening, but I've seen it lots and I know
how to fix it.
What you need to do is to add format switches to the MAILMERGE fields in
the Word document. Let's say you have a field called 'Cost' which leaves
Excel as =A319.99 or similar, but turns up in Word as 19.991234. Here's
how to fix it.
In the word document, start by keying Alt+F9 to expose all the field
codes. Look for the one that says:
{MERGEFIELD Cost }=20
There may or may not be quotes around the word 'Cost' - it doesn't
matter either way. Put your cursor between the end of the word 'Cost'
(after the quotes if there are any) and before the closing curly bracket
and type the following:
\# =A3##.00
The full field should now read:
{MERGEFIELD Cost \# =A3##.00}
This next bit is IMPORTANT. Do a CRTL+A and then a F9 to update all
fields. Now do Alt+F9 to switch back to field values instead of codes.
That should sort it.
There are a whole range of field switches like this. Word help has very
good coverage - look for 'time and date pictures' or 'number pictures'
or if all else fails, send Clippy looking for 'field switches' and
you'll find it from there.
Ray Blake
_____________
This email is from Ray Blake, Head of Software Design, GR Business
Process Solutions. It is confidential and intended for the addressee
only. The contents are private and may be legally privileged. If you
receive this email in error we would be grateful if you would advise the
sender and delete the email from your system.
For more information on the services that we offer please visit us at
our website: - www.grbps.com=20
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Anne Robson
Sent: 21 August 2003 12:11
To: mso@xxxxxxxxxxxxx
Subject: [mso] Excel and Word mailmerge - number formatting import
problems
Hi all
I have been using Excel and Word to mailmerge data for years and yet
have been=20
completely stopped in my tracks by the problems I have had in my present
task. I'm using Office 2002 BTW.
I have a large amount of data, some text, some currency, some dates,
some=20
general number data stuff. My problems are as follows:
1. Some of the dates (formatted as the equivalent of "d mmmm yyyy"
fields)=20
import from Excel into Word quite successfully. Others import as the
date=20
value codes (eg 58947 or whatever). Why? I have tried retyping,
overtyping=20
etc and the only way I can seem to fix it is by formatting the cells as
plain=20
text, which then means I can't sort on them by date order.
2. Some of the currency fields which are set to GB sterling with
currency=20
symbol in Excel, 2 decimal places, then import into Word with lots of
extra=20
decimal places which may or may not be un-rounded. It means that each
time I=20
want to check my figures I have to go back into a large Excel sheet and
see=20
whether I have got =A334,950.95 or =A334,950.94699999! Again, why is =
this
and=20
what can I do to fix it?
3. Similar problem to (2) - percentages seem to gain an extra shedload
of=20
decimal places, which also means I have to go back and check the
original data.
4. Some of the currency fields import across into Word with the
currency=20
symbol, others do not. *scratches head to work this one out*
The frustration factor in this is huge as this data is used to provide=20
correspondence which has to meet a legal compliance test. We cannot -=20
absolutely never, not ever, no way - have any of the info incorrect.
All suggestions gratefully received. I am rapidly losing my sense of
humour=20
with it! (but not quite, cos I know that some kind soul out there in
MSO-land=20
will have an answer for me - won't you? ;-)
Anne
----------------------------------------------
This mail sent through http://www.ukonline.net
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx
or MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to=20
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the
quotes) in the subject line.
Or, visit the group's homepage and use the dropdown menu. This will
also allow you to change your email settings to digest or vacation (no
mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group,
send a request to mso-moderators@xxxxxxxxxxxxx and you will be sent an
invitation with instructions. Once you are a member of the files group,
you can go here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in
the subject line.
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
