Problems merging in last 4 digits of an account higher than 16 digits

Glynda · #1 04-07-2011, 02:51 PM

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?

macropod · #2 04-08-2011, 12:17 AM

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'.

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