Word mail merge thousands separators and optional decimal places

Question

I'm working on importing values from an Excel sheet into word with the mail merge option, but I'm lost at how to format the numbers in word.

• 530

• 8600780.8

I want to display the values in the word document with thousands separators (preferrably spaces) and only show the decimals if it is not .00. In the end, I'm looking for something like

• 530 € and

• 8 600 780.80 €

I was able to format the values in excel in the above format wit conditional formatting. I have already tried importing the Excel worksheet via DDE, but using that I still get "530.00" instead of no decimals.

I've also tried formatting the mail merge fields directly, but in that case I see no way to optionally display the decimals.

I am using Office 365.

Appreciate all the help! Thanks!

Answer 1

If you can add a helper column in Excel that shows wether you have a whole number or decimals - you can use this column for a {IF THEN ELSE} in Word in which you set the number format accordingly (like conditional formatting in Excel):

Formula for the helper column "wholeNumber": =INT(A2) =A2

Field in Word: {IF {MERGEFIELD WholeNumber} = True "{mergefield Value \# "#.##0 €" " "{mergefield Value \# "#.##0,00 €"}" }

Please be aware that you can't copy/paste the Word field from this answer. Instead you have to enter the curly brackets with Ctrl+F9