#1
|
|||
|
|||
Merge field return last 4 digits of a number
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 |
#2
|
||||
|
||||
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
Code:
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
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
||||
|
||||
I thought you might have a tortuous field construction up your sleeve, but I think the macro is simpler
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#5
|
||||
|
||||
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: Code:
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
||||
|
||||
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.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#7
|
||||
|
||||
Running my macro as part of the mailmerge would require no more than:
Code:
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Unable to insert line break or carriage return in mail merge field | shahid.majeed | Mail Merge | 4 | 09-19-2019 01:08 AM |
Entering a number of 19 digits | Jo Freeman | Excel | 11 | 11-08-2017 06:20 AM |
Having number's digits together | mohsen.amiri | Word | 0 | 06-23-2017 01:20 AM |
How do I create a field that will return the current paragraph number minus one in an automatic list | gugootz | Word | 1 | 11-23-2015 04:58 PM |
Use a numerical merge field to subtract that number of months | MichaelSpedding | Mail Merge | 8 | 11-11-2015 01:11 PM |