Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Problems merging in last 4 digits of an account higher than 16 digits Problems merging in last 4 digits of an account higher than 16 digits
Register FAQ Mark Forums Read

Reply
 
Thread Tools Display Modes
  #1  
Old 04-07-2011, 02:51 PM
Glynda Glynda is offline Problems merging in last 4 digits of an account higher than 16 digits Windows 7 32bit Problems merging in last 4 digits of an account higher than 16 digits Office 2007
Novice
Problems merging in last 4 digits of an account higher than 16 digits 		 
Join Date: Mar 2011
Location: Nashville, TN
Posts: 2
Glynda is on a distinguished road
Smile Problems merging in last 4 digits of an account higher than 16 digits

I'm having problems merging in the last 4 digits of a 17 digit number. I found a switch that works for a 16 digit number but when the number is higher than 16 digits it rounds the numbers. Do you know if there switch format that will pull in the last 4 digits of a 17 digit number without changing the excel data file?
Reply With Quote
  #2  
Old 04-08-2011, 12:17 AM
macropod's Avatar
macropod macropod is offline Problems merging in last 4 digits of an account higher than 16 digits Windows 7 32bit Problems merging in last 4 digits of an account higher than 16 digits Office 2000
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
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
Hi Glynda,

Strictly speaking, Word can only handle numbers up to 15-digits. There is no switch that can be applied to get from there to 17 digits. However, the field code below will allow you to go all the way to 20 digits. Now, before you give yourself a heart-attack trying to digest and encode it, go to:
http://www.gmayor.com/export_field.htm#TextToField
where you can download a macro that will convert the text representation of the field code into a working one.

Done that? OK, here's the field code:

Code:
{QUOTE{SET Data {MERGEFIELD Value}}{=INT(Data/1000000)}
{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}}}}}}}}}
{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}}}}}}}}} \* Charformat}
Looks rather intimidating, doesn't it! Here's the same coding broken down into its component parts, and an explanation of how things work:

{SET Data {MERGEFIELD Value}}
This simply uses a SET field to create a bookmarked copy of your mailmerge value. Substitute the real name for 'Value'.

{=INT(Data/1000000)}
This reduces the field length to the 15-digit maximum that Word can handle, stripping off any leading 0s, and displays whatever's left.

{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}}}}}}}}}
This set of 9 nested IF fields tests the 6th-last digit in the field and displays whatever number it is.

{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}}}}}}}}}
This set of 9 nested IF fields tests the 5th-last digit in the field and displays whatever number it is.

{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}}}}}}}}}
This set of 9 nested IF fields tests the 4th-last digit in the field and displays whatever number it is.

{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}}}}}}}}}
This set of 9 nested IF fields tests the 3rd-last digit in the field and displays whatever number it is.

{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}}}}}}}}}
This set of 9 nested IF fields tests the 2nd-last digit in the field and
displays whatever number it is.

{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}}}}}}}}}
This set of 9 nested IF fields tests the last digit in the field and displays whatever number it is.

Each set of 9 nested IF fields is basically a compilation 9 sets of:
{IF{REF Data}= "*#?" # !},
where the:
. field braces (i.e. '{ }') are created in paird via Ctrl-F9;
. '*' is a wildcard representing all numbers in the field preceding the number you're looking for;
. '#' is the number you're looking for;
. '?' is a wildcard representing each succeeding number in the field; and
. '!' represents the next number or nested IF test.

I've used a QUOTE field to group the whole lot together (eg for easier re-positioning as a group), followed by a Charformat switch at the end of the field. This means that, if you have a particular format that you want to apply to the output (eg a 24pt 3of9 barcode), all you need to do is to apply that attribute to the 'Q' in 'QUOTE'.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools
Display Modes
Linear Mode Linear Mode


Similar Threads
Thread Thread Starter Forum Replies Last Post
Merging Projects C_Howard Project 2 03-29-2012 02:00 PM
How to delete first 6 Digits and one Space from a CELL? Learner7 Excel 3 08-10-2010 04:06 AM
Merging or Cut and Paste AnnieM Word 1 06-24-2010 03:03 AM
Help with merging? yazakib Mail Merge 0 04-11-2010 01:24 PM
Merging documents Stattovic Word 7 01-31-2010 03:06 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:49 AM.

Contact Us - Privacy Statement - Top

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