Merge field return last 4 digits of a number

[macquhele]

Hi there. I am looking for a merge switch that will return the last 4 digits of a 16 digit number. I've searched a number of threads and forums on this topic and found the following:


{=MOD({MERGEFIELD ACCOUNT_NUMBER },10000 \#0000 }
However, I am finding that the switch rounds the last of the 4 digits up. I attempted to include a minus 1 calculation in the switch but this only works for numbers 0 to 8 because number 9 returns a zero. As an example if the last 4 digits is 1239 the switch returns 1230 so deducting 1 makes it 1229. Does anyone know if there is a way to fix this?
Thank you in advance of any help.
Regards. Mac

[gmayor]

You can't merge part of a field, but if you were to use https://www.gmayor.com/email_merge_addin.html to merge to separate documents, you could use the following macro with it to get the last four digits of any 16 digit number

Sub LastFour(oDoc As Document)
Dim oNum As Range
    Set oNum = ActiveDocument.Range
    With oNum.Find
        Do While .Execute(findText:="[0-9]{16}", MatchWildcards:=True)
            oNum.Text = Right(oNum, 4)
            oNum.Collapse 0
        Loop
    End With
lbl_Exit:
    Set oNum = Nothing
    Exit Sub
End Sub

[macropod]

Originally Posted by gmayor

You can't merge part of a field

In the case of numeric fields of up to 15 digits, not so. The problem here is the 16th digit, for which a suitable field construction is:

{QUOTE{SET Data {MERGEFIELD ACCOUNT_NUMBER}}
{IF{REF Data }= "*1???" 1 {IF{REF Data }= "*2???" 2 {IF{REF Data }= "*3???" 3 {IF{REF Data }= "*4???" 4 {IF{REF Data }= "*5???" 5 {IF{REF Data }= "*6???" 6 {IF{REF Data }= "*7???" 7 {IF{REF Data }= "*8???" 8 {IF{REF Data }= "*9???" 9 0}}}}}}}}}
{IF{REF Data }= "*1??" 1 {IF{REF Data }= "*2??" 2 {IF{REF Data }= "*3??" 3 {IF{REF Data }= "*4??" 4 {IF{REF Data }= "*5??" 5 {IF{REF Data }= "*6??" 6 {IF{REF Data }= "*7??" 7 {IF{REF Data }= "*8??" 8 {IF{REF Data }= "*9??" 9 0}}}}}}}}}
{IF{REF Data }= "*1?" 1 {IF{REF Data }= "*2?" 2 {IF{REF Data }= "*3?" 3 {IF{REF Data }= "*4?" 4 {IF{REF Data }= "*5?" 5 {IF{REF Data }= "*6?" 6 {IF{REF Data }= "*7?" 7 {IF{REF Data }= "*8?" 8 {IF{REF Data }= "*9?" 9 0}}}}}}}}}
{IF{REF Data }= "*1" 1 {IF{REF Data }= "*2" 2 {IF{REF Data }= "*3" 3 {IF{REF Data }= "*4" 4 {IF{REF Data }= "*5" 5 {IF{REF Data }= "*6" 6 {IF{REF Data }= "*7" 7 {IF{REF Data }= "*8" 8 {IF{REF Data }= "*9" 9 0}}}}}}}}}}

For a macro to convert the above into a working field, see Convert Text Representations of Fields to Working Fields in the Mailmerge Tips and Tricks 'Sticky' thread at the top of this forum:
https://www.msofficeforums.com/mail-...ps-tricks.html

[gmayor]

I thought you might have a tortuous field construction up your sleeve, but I think the macro is simpler

[macropod]

A simpler field construction might also do the job:
{QUOTE{SET Data {MERGEFIELD ACCOUNT_NUMBER}}
{=MOD(INT(Data/100000000000),1000) \# 0}
{IF{REF Data }= "*1" 1 {IF{REF Data }= "*2" 2 {IF{REF Data }= "*3" 3 {IF{REF Data }= "*4" 4 {IF{REF Data }= "*5" 5 {IF{REF Data }= "*6" 6 {IF{REF Data }= "*7" 7 {IF{REF Data }= "*8" 8 {IF{REF Data }= "*9" 9 0}}}}}}}}}}

But, if it's a macro you want, it can be much simpler and faster than yours, too:

Sub LastFour()
Application.ScreenUpdating = False
ActiveDocument.Range.Find.Execute FindText:="[0-9]{12}([0-9]{4})", ReplaceWith:="\1", MatchWildcards:=True, Replace:=wdReplaceAll
Application.ScreenUpdating = True
End Sub

It's also not apparent why you pass 'oDoc As Document' to the macro, meaning it can't be run via Alt-F8, but don't then do anything with it.

[gmayor]

The code was intended to be called from https://www.gmayor.com/email_merge_addin.html to perform the conversion on the fly, rather than to be run separately. It could of course be easily modified to work as a stand alone macro, as indeed can your macro be modified to be used with the process above where the relative speed of the two macros would be inconsequential. Both work.

[macropod]

Running my macro as part of the mailmerge would require no more than:

Sub MailMergeToDoc()
Application.ScreenUpdating = False
ActiveDocument.Mailmerge.Execute
ActiveDocument.Range.Find.Execute FindText:="[0-9]{12}([0-9]{4})", ReplaceWith:="\1", MatchWildcards:=True, Replace:=wdReplaceAll
Application.ScreenUpdating = True
End Sub

Execution would then be via Mailings>Finish & Merge>Edit Individual Documents, the same as with any regular merge. Regardless, a macro really isn't needed...