Mail Merge

MrBlack · #1 11-26-2021, 06:40 AM

So I am not much of an office power user and never use mail merge but I have been asked by a colleague for some help on masking/formatting the output of some data in a mail merge.

I have searched on the internet and found some helpful examples but haven't found exactly what they need or have failed to understand how this is done in the examples I've seen on various pages.

Long story short is they have the following example data format:

Account number: 12345678
*Always 8 numbers in length*

Sort Code: 11-22-33
*Always 3 sets of double digit numbers separated by two hyphens*

They are only wanting to display/merge the first 4 digits of the "Account Number" and only display the last 2 digits of the "Sort Code".

I have found examples for showing the last 4 digits and tested this and got it working but I cannot see how you would go about doing the opposite:

Code:

//Example showing last 4 digits of "Account Number"
{ MERGEFIELD "Account_Number" \#\x###}

I've not found anything similar for showing the last two numbers of the "Sort Code" given the formatting of it with the hyphens.

If anyone could offer any advice or point me in the right direction (or website) that would be greatly appreciated.

gmayor · #2 11-26-2021, 10:26 PM

The short answer is that this is not possible using regular mail merge, because you cannot use only part of a field. You could do it with a macro, and you could use e.g.
E-Mail Merge Add-in
or
https://www.gmayor.com/MergeAndSplit.htm

to merge the document to separate documents and process those documents on the fly to format the account number and the sort code.

To facilitate that, add unique tags to each of the fields e.g.

%%{ MERGEFIELD Account_Number }%% &&{ MERGEFIELD Sort_Code }&&

Here I have added %% to either side of the account number field and && either side of the sort code each time they are used in the document.

Run the following macro from the merge process:

Code:

Sub FormatAC(oDoc As Document)
Dim oDocRng As Range
    Set oDocRng = oDoc.Range
    With oDocRng.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        Do While .Execute(findText:="%%")
            oDocRng.Text = ""
            oDocRng.MoveEndUntil "%"
            oDocRng.End = oDocRng.End + 2
            oDocRng.Text = Left(oDocRng.Text, 4) & "..."
            oDocRng.Collapse 0
        Loop
    End With
    Set oDocRng = oDoc.Range
    With oDocRng.Find
        Do While .Execute(findText:="&&")
            oDocRng.Text = ""
            oDocRng.MoveEndUntil "&"
            oDocRng.End = oDocRng.End + 2
            oDocRng.Text = "..." & Mid(oDocRng.Text, 7, 2)
            oDocRng.Collapse 0
        Loop
    End With
    Set oDocRng = Nothing
End Sub

When merged the result would be
1234... ...33

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Formatting numbers in text box using Mail Merge	Rozza	Word	1	11-17-2019 09:57 AM
Formatting numbers in a mail merge	Dibble	Mail Merge	5	01-04-2016 04:17 PM
Mail Merge Formatting Phone numbers	Marie_Melody	Mail Merge	4	07-02-2014 04:22 PM
Formatting changes after mail merge	staceynairn	Mail Merge	1	06-03-2014 06:29 PM
Formatting in mail merge document	OnTheCouch	Mail Merge	1	09-29-2013 07:05 PM