Mail Merge, Number to 2 Decimal Places

[Vampy99]

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

[macropod]

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.

[Vampy99]

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

[macropod]

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

[zeddock]

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

[macropod]

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.

[Vampy99]

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

[macropod]

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}