Mail Merge - Keeping accounting format

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hey all,

I haven't used mail merge very much, and I have the need to keep the accounting format of a cell when it is merged into a word document. Basically, I need to make sure the dollar signs and decimals always line up.

My initial thought was to create a helper column that stores a value as a string, but I don't think that will work properly, since a space isn't the same width as other characters.

ex:
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">775.34</td><td style="text-align: right;;">$ 775.34</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">9999999.99</td><td style="text-align: right;;">$ 9999999.99</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1003.56</td><td style="text-align: right;;">$ 1003.56</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B7</th><td style="text-align:left">="$" & REPT(<font color="Blue">" ",15-LEN(<font color="Red">A7</font>)</font>) & TEXT(<font color="Blue">A7,"0.00"</font>)</td></tr></tbody></table></td></tr></table><br />

Any ideas would be much appreciated!
 
Last edited:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
You will need to format the field in Word!!
Do a search for "switches". That should get you started!

lenze
 
Upvote 0
Ahh - Thanks for that info, Lenze.

So would I want to make the switch be something like this?
{MERGEFIELD "Amount" \@ "_($* #,##0.00_)"}

Or would it be like:
{MERGEFIELD Amount \# _($* #,##0.00_)}
 
Last edited:
Upvote 0
I have tried a few different switches, and I can't seem to get the results I am looking for.



I've tried:
  • {MERGEFIELD "Amount" /# "$* #,##0.00"}
  • {MERGEFIELD "Amount" /# "_($* #,##0.00_)"}
  • {MERGEFIELD "Amount" /@ "$* #,##0.00"}
  • {MERGEFIELD "Amount" /# "$ #,##0.00"}
The last one gets me the closest, but when I still can't get the decimals to line up properly. For example:

If I have the values 9999999.99 and 5142.50 (for two different fields), and I want to show them one under the other, I get:
Code:
$ 9,999,999.99
$ 5,142.50

when I want it to look like:
Code:
$ 9,999,999.99
$     5,142.50
 
Upvote 0
Update:

I figured it out.

I insertted a right-tab stop on the lines that have the dollar amounts, then I applied the following switch:

{MERGEFIELD Amount \# "$(ctrl-tab)#,##0.00"}

Thanks again, everyone!
 
Upvote 0

Forum statistics

Threads
1,224,527
Messages
6,179,345
Members
452,907
Latest member
Roland Deschain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top