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: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
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: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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
[Fmr 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: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
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: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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
[Fmr 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: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
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: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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
[Fmr 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

All times are GMT -7. The time now is 11:53 AM.


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