Microsoft Office Forums - View Single Post - Mail Merge

gmayor · 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