Using financials in Mail Merges

Imagine you have a financial figure that you want to use in a mail merge (a salary, bonus, house prices etc.)

If you use them as they stand, they'll likely look rubbish. They won't show the currency and there won't be a lovely comma separating the thousands from the sub-thousands. Instead, create a new field containing the following formula (assuming the financial field you want to use is in B2):

="£"&ROUNDDOWN(B2/1000,0)&","&MOD(B2,1000)

Let's say the salary was £32,500. The ROUNDDOWN function will divide your salary by 1,000 and give you that figure—in this case 32. The MOD function also divides your salary by 1,000 but instead gives you the remainder—in this case 500. The & function is a shortcut for the CONCATENATE function, stringing multiple entries together. So it adds the £ at the beginning and the comma midway.

The formula would need some additional logic if the numbers were to stretch into the millions, but you get the picture.

• Tweet