[mso] Re: Excel and Word mailmerge - number formatting import problems
- From: Anne Robson <anne.robson@xxxxxxxxxxxxxx>
- To: mso@xxxxxxxxxxxxx
- Date: Fri, 22 Aug 2003 14:35:28 +0100
Ray you are an absolute star. It's taken a bit of tweaking but all the
currency and percentages are now working properly - I haven't yet had time to
play with the date fields tho!
Many MANY thanks.
Anne
--
"There are two means of refuge from the miseries of life: music and cats."
- Albert Schweitzer
Quoting Ray Blake <ray@xxxxxxxxx>:
> 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
> *************************************************************
>
----------------------------------------------
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
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:
