A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Mailmerge
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Round number down in mail merge



 
 
Thread Tools Display Modes
  #1  
Old April 4th, 2010, 04:32 PM posted to microsoft.public.word.mailmerge.fields
Andy Roberts
external usenet poster
 
Posts: 183
Default Round number down in mail merge

I have a number being merged from excel which is formatted to 1 decimal
place in excel (e.g. 1.6). When it comes through to word as part of the
mail merge it displays as a true number (1.666666666666667) and I want it to
display to 1dp. I'm aware of switches to control formatting but don't know
how to do this with number formats / decimal places within the word doc.

Can anyone shed light on it?

--
Regards

Andy

Andy Roberts
Win XP, Office 2007


  #2  
Old April 4th, 2010, 06:07 PM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
Default Round number down in mail merge

try

{ =int({ MERGEFIELD mynumber }*10)/10 }

where all the {} are the special field code brace pairs that you can
insert using ctrl-F9, and "mynumber" is the name of your mergefield.

Peter Jamieson

http://tips.pjmsn.me.uk

On 04/04/2010 16:32, Andy Roberts wrote:
I have a number being merged from excel which is formatted to 1 decimal
place in excel (e.g. 1.6). When it comes through to word as part of the
mail merge it displays as a true number (1.666666666666667) and I want it to
display to 1dp. I'm aware of switches to control formatting but don't know
how to do this with number formats / decimal places within the word doc.

Can anyone shed light on it?

  #3  
Old April 4th, 2010, 08:00 PM posted to microsoft.public.word.mailmerge.fields
Andy Roberts
external usenet poster
 
Posts: 183
Default Round number down in mail merge

Peter

Thanks for your response. I get a syntax error when I use your suggestion.
I add a field code using ctrl+F9 then insert your suggestion minus the first
and last {}.

--
Regards

Andy

Andy Roberts
Win XP, Office 2007
"Peter Jamieson" wrote in message
...
try

{ =int({ MERGEFIELD mynumber }*10)/10 }

where all the {} are the special field code brace pairs that you can
insert using ctrl-F9, and "mynumber" is the name of your mergefield.

Peter Jamieson

http://tips.pjmsn.me.uk

On 04/04/2010 16:32, Andy Roberts wrote:
I have a number being merged from excel which is formatted to 1 decimal
place in excel (e.g. 1.6). When it comes through to word as part of the
mail merge it displays as a true number (1.666666666666667) and I want it
to
display to 1dp. I'm aware of switches to control formatting but don't
know
how to do this with number formats / decimal places within the word doc.

Can anyone shed light on it?



  #4  
Old April 4th, 2010, 10:54 PM posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP
external usenet poster
 
Posts: 8,239
Default Round number down in mail merge

If it can be done, it will almost certainly be in fellow MVP Macropod's Word
Field Maths Tutorial at:

http://lounge.windowssecrets.com/ind...owtopic=365442

or at:

http://www.gmayor.com/downloads.htm#Third_party

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"Andy Roberts" wrote in message
...
Peter

Thanks for your response. I get a syntax error when I use your
suggestion. I add a field code using ctrl+F9 then insert your suggestion
minus the first and last {}.

--
Regards

Andy

Andy Roberts
Win XP, Office 2007
"Peter Jamieson" wrote in message
...
try

{ =int({ MERGEFIELD mynumber }*10)/10 }

where all the {} are the special field code brace pairs that you can
insert using ctrl-F9, and "mynumber" is the name of your mergefield.

Peter Jamieson

http://tips.pjmsn.me.uk

On 04/04/2010 16:32, Andy Roberts wrote:
I have a number being merged from excel which is formatted to 1 decimal
place in excel (e.g. 1.6). When it comes through to word as part of the
mail merge it displays as a true number (1.666666666666667) and I want
it to
display to 1dp. I'm aware of switches to control formatting but don't
know
how to do this with number formats / decimal places within the word doc.

Can anyone shed light on it?



  #5  
Old April 5th, 2010, 02:43 AM posted to microsoft.public.word.mailmerge.fields
macropod[_2_]
external usenet poster
 
Posts: 2,402
Default Round number down in mail merge

Hi Andy,

Peter's code had *two* fields - one inside the other. Thus, you need to press Ctrl-F9 twice to create a nested pair of fields (ie
'{{ }}') then fill in/around them to get:
{=INT({ MERGEFIELD mynumber}*10)/10 \# 0.0}


--
Cheers
macropod
[Microsoft MVP - Word]


"Andy Roberts" wrote in message ...
Peter

Thanks for your response. I get a syntax error when I use your suggestion. I add a field code using ctrl+F9 then insert your
suggestion minus the first and last {}.

--
Regards

Andy

Andy Roberts
Win XP, Office 2007
"Peter Jamieson" wrote in message ...
try

{ =int({ MERGEFIELD mynumber }*10)/10 }

where all the {} are the special field code brace pairs that you can insert using ctrl-F9, and "mynumber" is the name of your
mergefield.

Peter Jamieson

http://tips.pjmsn.me.uk

On 04/04/2010 16:32, Andy Roberts wrote:
I have a number being merged from excel which is formatted to 1 decimal
place in excel (e.g. 1.6). When it comes through to word as part of the
mail merge it displays as a true number (1.666666666666667) and I want it to
display to 1dp. I'm aware of switches to control formatting but don't know
how to do this with number formats / decimal places within the word doc.

Can anyone shed light on it?




  #6  
Old April 5th, 2010, 08:25 AM posted to microsoft.public.word.mailmerge.fields
Andy Roberts
external usenet poster
 
Posts: 183
Default Round number down in mail merge

Thanks guys - what would the mail merge community do without you all!

--
Regards

Andy

Andy Roberts
Win XP, Office 2007
"Andy Roberts" wrote in message
...
Peter

Thanks for your response. I get a syntax error when I use your
suggestion. I add a field code using ctrl+F9 then insert your suggestion
minus the first and last {}.

--
Regards

Andy

Andy Roberts
Win XP, Office 2007
"Peter Jamieson" wrote in message
...
try

{ =int({ MERGEFIELD mynumber }*10)/10 }

where all the {} are the special field code brace pairs that you can
insert using ctrl-F9, and "mynumber" is the name of your mergefield.

Peter Jamieson

http://tips.pjmsn.me.uk

On 04/04/2010 16:32, Andy Roberts wrote:
I have a number being merged from excel which is formatted to 1 decimal
place in excel (e.g. 1.6). When it comes through to word as part of the
mail merge it displays as a true number (1.666666666666667) and I want
it to
display to 1dp. I'm aware of switches to control formatting but don't
know
how to do this with number formats / decimal places within the word doc.

Can anyone shed light on it?





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.