Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-30-2020, 04:15 AM
macquhele macquhele is offline Merge field return last 4 digits of a number Windows 10 Merge field return last 4 digits of a number Office 2016
Novice
Merge field return last 4 digits of a number
 
Join Date: Jul 2020
Posts: 1
macquhele is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 07-31-2020, 10:39 PM
gmayor's Avatar
gmayor gmayor is offline Merge field return last 4 digits of a number Windows 10 Merge field return last 4 digits of a number Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,275
gmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of light
Default

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
Reply With Quote
  #3  
Old 08-01-2020, 04:01 AM
macropod's Avatar
macropod macropod is offline Merge field return last 4 digits of a number Windows 7 64bit Merge field return last 4 digits of a number Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,464
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Quote:
Originally Posted by gmayor View Post
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
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #4  
Old 08-01-2020, 06:49 AM
gmayor's Avatar
gmayor gmayor is offline Merge field return last 4 digits of a number Windows 10 Merge field return last 4 digits of a number Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,275
gmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of light
Default

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
Reply With Quote
  #5  
Old 08-01-2020, 04:06 PM
macropod's Avatar
macropod macropod is offline Merge field return last 4 digits of a number Windows 7 64bit Merge field return last 4 digits of a number Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,464
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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
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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #6  
Old 08-01-2020, 09:26 PM
gmayor's Avatar
gmayor gmayor is offline Merge field return last 4 digits of a number Windows 10 Merge field return last 4 digits of a number Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,275
gmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of light
Default

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
Reply With Quote
  #7  
Old 08-01-2020, 11:04 PM
macropod's Avatar
macropod macropod is offline Merge field return last 4 digits of a number Windows 7 64bit Merge field return last 4 digits of a number Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,464
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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
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...
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Merge field return last 4 digits of a number 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
Merge field return last 4 digits of a number 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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 03:00 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft