Mail merge - figures, comma and zero (fields)
en2mots
-
Argitxu Posted messages 5292 Registration date Status Contributeur Last intervention -
Argitxu Posted messages 5292 Registration date Status Contributeur Last intervention -
Hello,
I have a mail merge to do in which amounts and quantities must appear. Here’s what I can't get right:
Empty cells in Excel are reported in the mail merge as follows: 0 or ,00
Amounts from my Excel database are reported incorrectly, for example, 1525.12 becomes 1525.1111119...
What I would like is for no information to be reported for an empty cell.
And amounts should have no thousands separator, for example: I have xxxxx instead of xx xxx
We talk about putting codes to modify the fields, but I've read so many different codes to use and each time it's not clear where exactly to put them, nor the syntax, nor if they all go in the same place... can someone give me an example or describe this to me in detail, even if it means making screenshots on Word... Thank you in advance!
I have a mail merge to do in which amounts and quantities must appear. Here’s what I can't get right:
Empty cells in Excel are reported in the mail merge as follows: 0 or ,00
Amounts from my Excel database are reported incorrectly, for example, 1525.12 becomes 1525.1111119...
What I would like is for no information to be reported for an empty cell.
And amounts should have no thousands separator, for example: I have xxxxx instead of xx xxx
We talk about putting codes to modify the fields, but I've read so many different codes to use and each time it's not clear where exactly to put them, nor the syntax, nor if they all go in the same place... can someone give me an example or describe this to me in detail, even if it means making screenshots on Word... Thank you in advance!
1 réponse
Good evening
Two possibilities:
1 - to correct on all merges from today and in the future
- In the options / advanced options, general section: check: confirm file conversion.
- Redo the merge to link the two files and choose the DDE protocol at the prompt
2 - On your current file, by forcing the format in the Word merge document field
- Press ALT + F9 to show field codes
- complete the one that brings back the values like this
{ MERGEFIELD Montant \# "# ###,00" }
- re-press ALT + F9 to hide the field code
--
Argitxu
Two possibilities:
1 - to correct on all merges from today and in the future
- In the options / advanced options, general section: check: confirm file conversion.
- Redo the merge to link the two files and choose the DDE protocol at the prompt
2 - On your current file, by forcing the format in the Word merge document field
- Press ALT + F9 to show field codes
- complete the one that brings back the values like this
{ MERGEFIELD Montant \# "# ###,00" }
- re-press ALT + F9 to hide the field code
--
Argitxu
The field code above in solution 2 properly forces the display of merged numbers with thousands and 2 decimals to give 1,200.00. We won't revisit that.
Have you updated the field after the modification: ctrl+A to select then F9
Otherwise, if you're not comfortable with field codes, choose the 1st solution with the DDE protocol
The empty Excel cells are taken as 0 by Word. Here, Word doesn't invent anything. You need to check the source. You must have hidden the display of 0 in the Excel file, which makes you think the cell is empty. Even when hidden in Excel, Word retrieves them.
If the referenced field is the result of a formula, you can modify it in Excel by making it show nothing if the value is equal to 0 like:
if(A2="" ; "" ; yourformula).
You can also do this in the Word merge document on the field in question
{IF {Amount}=0 "" {Amount \# "# ###,00"}}
Argitxu