Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-20-2011, 01:03 AM
Vampy99 Vampy99 is offline Mail Merge, Number to 2 Decimal Places Windows 7 32bit Mail Merge, Number to 2 Decimal Places Office 2007
Novice
Mail Merge, Number to 2 Decimal Places
 
Join Date: Jul 2011
Posts: 4
Vampy99 is on a distinguished road
Default Mail Merge, Number to 2 Decimal Places

Hi,

I have some numbers on a mail merge that I want to show to 2 decimal places, however if the second decimal place is a zero then the formula I use only shows to 1 decimal place.



The 2 formulas I have been using are below;

£{ MERGEFIELD "NUMBER" \# "#,##0.0x" }

£{ MERGEFIELD "NUMBER" \#0.0x }

Both formulas result in the same problem, see example below;

MERGEFIELD "NUMBER" is 102 let's say. Both formulas display the number as £102.0

If the number is 102.01 then it is correctly displayed as £102.01

These numbers are the results of formulas in Excel so the decimal places are more than to 2 places.

Thanks for any help in advance.

Luke
Reply With Quote
  #2  
Old 07-20-2011, 02:03 AM
macropod's Avatar
macropod macropod is online now Mail Merge, Number to 2 Decimal Places Windows 7 64bit Mail Merge, Number to 2 Decimal Places Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Luke,

Try a field coded as:
{QUOTE{SET Val {MERGEFIELD MyField \# 0.00}}{IF{REF Val}= {=Val \# 0} {=Val \# "£,0"} {IF{REF Val}= {=Val \# 0.0} {=Val \# £,0.0} {=Val \# £,0.00}}}}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 07-21-2011, 02:58 AM
Vampy99 Vampy99 is offline Mail Merge, Number to 2 Decimal Places Windows 7 32bit Mail Merge, Number to 2 Decimal Places Office 2007
Novice
Mail Merge, Number to 2 Decimal Places
 
Join Date: Jul 2011
Posts: 4
Vampy99 is on a distinguished road
Default

Hi Paul,

Thanks for your response.

I've just given this a go but I can't work out how to implement this into the mergefield.

If it's not too much trouble could you explain it to me a little deeper?

Regards,

Luke
Reply With Quote
  #4  
Old 07-21-2011, 03:52 AM
macropod's Avatar
macropod macropod is online now Mail Merge, Number to 2 Decimal Places Windows 7 64bit Mail Merge, Number to 2 Decimal Places Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Luke,

If you copy the 'code' I posted and paste it into your document and replace 'My Field' with whatever your problem data field's name is, there's a macro I wrote to convert it to a working field at:
http://www.gmayor.com/export_field.htm#TextToField
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 08-23-2011, 01:09 PM
zeddock zeddock is offline Mail Merge, Number to 2 Decimal Places Windows 7 64bit Mail Merge, Number to 2 Decimal Places Office 2010 64bit
Novice
 
Join Date: Aug 2011
Posts: 1
zeddock is on a distinguished road
Default

Basic switches are not working in the actual merge..... the preview looks correct, but I still get many decimal places when I do the actual merge.

I used a switch code of: \# "0.00"

It limits the excel spreadsheet to 2 decimals on a preview, but a print or edit of merged files, still shows the long decimals...

We need to change our data source spreadsheet on a regular basis and would like to not have to update all of this in the future.

Is there something we can do?

Thanx
!



zeddock
Reply With Quote
  #6  
Old 08-23-2011, 04:28 PM
macropod's Avatar
macropod macropod is online now Mail Merge, Number to 2 Decimal Places Windows 7 64bit Mail Merge, Number to 2 Decimal Places Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Cross-posted at: http://social.technet.microsoft.com/...-ab4496e3dee9/

For cross-posting etiquette, please read: http://www.excelguru.ca/node/7

Unless you forgot to save the mailmerge main document with the switches in place, I cannot imagine how you could get different final results than what the preview shows.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 09-23-2011, 03:39 AM
Vampy99 Vampy99 is offline Mail Merge, Number to 2 Decimal Places Windows 7 32bit Mail Merge, Number to 2 Decimal Places Office 2007
Novice
Mail Merge, Number to 2 Decimal Places
 
Join Date: Jul 2011
Posts: 4
Vampy99 is on a distinguished road
Default

Hi,

I finally found a fix for this, it involves a little bit of work in Excel.

Basically, find the position of the the decimal place in the number using the =FIND function.

Add 2 to this number.

Using the =LEFT function, select the number of characters which you have determined above.

In the mail merge itself, use switch \# "#,##0.00x" & this will show all of your numbers to 2 decimal places.

Hope this will aid other people who have had the same problem as me!

Regards,

Luke
Reply With Quote
  #8  
Old 09-25-2011, 05:41 AM
macropod's Avatar
macropod macropod is online now Mail Merge, Number to 2 Decimal Places Windows 7 64bit Mail Merge, Number to 2 Decimal Places Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

If all you needed was for all values to display to 2 decimal places, it could all be done with a simple field switch - without any changes to the data source:
{MERGEFIELD Number \# ,0.00}
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge, Number to 2 Decimal Places Saving INDV mail merges During the mail merge sedain121 Mail Merge 2 10-04-2011 07:52 PM
Mail Merge, Number to 2 Decimal Places Mail merge number fields outputting inconsistently rekcots Mail Merge 2 05-18-2011 06:32 AM
My Places lukewarmbeer Office 0 07-13-2010 02:33 PM
Mail Merge, Number to 2 Decimal Places Convert hours in decimal ghostones Excel 1 12-29-2009 09:17 PM
Merge, Too many Numbers right of the Decimal Point ohdearme Mail Merge 0 10-25-2009 05:10 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:29 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft