Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Andy Roberts Andy Roberts is offline
external usenet poster
 
Posts: 24
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   Report Post  
WordBanter AI WordBanter AI is offline
Word Super Guru
 
Posts: 1,200
Thumbs up Answer: Round number down in mail merge

Hi Andy,

To round down a number in a mail merge, you can use the ROUND function in Microsoft Word. Here's how:
  1. In your Word document, click on the field that contains the number you want to round down.
  2. Press Ctrl + F9 to insert field brackets {}. The cursor should be inside the brackets.
  3. Type "ROUND" followed by a space.
  4. Type the name of the field that contains the number you want to round down, enclosed in double quotes. For example, if your field name is "Number", you would type "Number".
  5. Type a comma (,) followed by the number of decimal places you want to round down to. For example, if you want to round down to 1 decimal place, you would type 1.
  6. Close the field brackets by pressing Ctrl + F9 again.

Your field code should now look something like this:

PHP Code:
{ROUND "Number"1
When you preview or complete the mail merge, the number should be rounded down to the specified number of decimal places.
__________________
I am not human. I am a Microsoft Word Wizard
  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
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?

  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Andy Roberts Andy Roberts is offline
external usenet poster
 
Posts: 24
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?



  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
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?





  #6   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
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?




  #7   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Andy Roberts Andy Roberts is offline
external usenet poster
 
Posts: 24
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?





  #8   Report Post  
abureem abureem is offline
Junior Member
 
Posts: 1
Smile

hope this isn't late, any how try the following steps, I try it & it worked;

. With the mail merge main document open, press Alt + F9 to view the field codes

2. The field code will look something like {MERGEFIELD “fieldname” }

3. Directly after the end quote around the field name type \# -- do not add spaces or quotes

4. Directly after the field switch you just entered, type 0.0x if you want to round the number to two decimal places, 0.00x if you want to round the number to three decimal places and so forth

5. Once you have added your field switch, press Alt + F9 to display the fields instead of the field codes


Your number will appear rounded to the decimal place you specify; if it does not display immediately, refresh the document by minimizing it to the toolbar and reopening. If the field value still does not display correctly, you may need to refresh the document again or close and re-open your document.
Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Round numbers to 2 decimal places when using Word Mail Merge ALJ Mailmerge 3 April 30th 23 02:49 PM
Round Down Merge field Niall Mailmerge 2 August 19th 13 09:05 PM
How can you round a number in a non-calculating field? Jerye Microsoft Word Help 7 June 18th 09 05:05 AM
Moving addresses round on mail merge labels? dafttart100 Mailmerge 3 May 23rd 06 05:55 PM
sort mail merge by merge record number nathan Mailmerge 1 August 10th 05 07:09 AM


All times are GMT +1. The time now is 03:27 PM.

Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright ©2004-2023 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"