Remove the leading zero from mergefield .

[Joergen Bondesen]

Hi NG.

I have a mailmerge field where the field from a txt-file display 20 digits:
'09812340078901230067'
I want only displayed 19 digits '9812340078901230067' because zero may not
be displayed.
It is not posssible to remove the leading zero in the txt-file.

 

[Doug Robbins - Word MVP]

Put the mergefield in a formula that mulitplies it by 1

{ = { MERGEFIELD fieldname } * 1 }

You must use Ctrl+F9 to insert the field delimiters { }

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Joergen Bondesen]

Hi Dough

Your reply maked med happy, but a test in XP shows:

Input: 09812340078901230067
Output 9812340078901200000

The leading Zero is removed but trailing 230067 => 200000
Does Word have a limitation of 14 / 15 digits?

 

[Peter Jamieson]

Does Word have a limitation of 14 / 15 digits?

Yes.

Which version(s) of Word are you using?

What is the specific reason that you cannot remove the leading 0 in the text
file (which may be the best way to do this)? Is it because you need to avoid
creating new files in the local file system, or do you just mean that you
have no control over how the file is created in the first place?

What is the exact format of the text file? Is it comma-delimited?
tab-delimited? Some other delimiter? And is the number enclosed in quotes or
not, e.g.

is it

k,num
1,"01234567890123456789"

k,num
1,01234567890123456789

or what?

Are all the numbers 20 digits long? Do you only ever need to strip a single
leading zero, or if there are more leading zeros, do you need to strip them
all?

If it's only one leading zero, would it be enough to overwrite the 0 by a
white-coloured zero, e.g. using something like

{ MERGEFIELD num }{ ADVANCE \l 130 }0{ ADVANCE \r 140 }

(You would need to colour that "0" white, and adjust the two values in the
ADVANCE fields, but because digits are always fixed width this should "work"
as long as you can use ADVANCE to move exactly the correct distance
leftwards. Tacky, but it might be the simplest approach)

Peter Jamieson

 

[Graham Mayor]

Peter also suggested another method of dealing with long numbers some time
ago which appears at http://www.gmayor.com/formatting_word_fields.htm under
the heading 'Number fields'

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[macropod]

Hi Joergen,

If you *really* can't change the source file, you could code your mailmerge
template like:
{SET Data {MERGEFIELD Value}}{=INT(Data/10^5)}{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}}}}}}}}}

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/10^5)}
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 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.

If you want to group the whole lot together (eg for easier re-positioning as
a group), you could embed them inside a QUOTE field.

Cheers

 

[Joergen Bondesen]

Hi Doug Robbins; Peter Jamieson; Graham Mayor and macropod

Thanks to all of you for all the answers.

I have a mailmerge field where the field from a txt-file
display 20 digits: "09812340078901230067","zz" and the next number is
"09812340078901230068","zz" etc.

Of course I can remove the 0 (zero) from the txt-file but it is daily, takes
time and I can forget it.

I am using the number on a carrier (letter with a card) with a font 3of9 so
i can have a barcode.

Ouer mailermachine read the barcode and compare the number with the number
on the card.
Cardnumber is 19 digits: 9812340078901230067 because the card is without 0
(zero).

I hope this explains my dilemma.

I will bee back with thanks or further ask for help.


Best regards
Joergen Bondesen

Hi Joergen,

If you *really* can't change the source file, you could code your
mailmerge
template like:
{SET Data {MERGEFIELD Value}}{=INT(Data/10^5)}{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}}}}}}}}}

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/10^5)}
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 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.

If you want to group the whole lot together (eg for easier re-positioning
as
a group), you could embed them inside a QUOTE field.

Cheers

--
macropod
[MVP - Microsoft Word]

Hi NG.

I have a mailmerge field where the field from a txt-file display 20 digits:
'09812340078901230067'
I want only displayed 19 digits '9812340078901230067' because zero may
not
be displayed.
It is not posssible to remove the leading zero in the txt-file.

 

[macropod]

Hi Joergen,

Is the "zz" part of the same field in your text file as the 20 digits
"09812340078901230067" (eg: "09812340078901230067zz"), or are they separate
fields (eg: "09812340078901230067" & "zz")?

Is the "zz" part of the barcode? If so, is it always "zz", or can other
letters be involved?

Cheers

--
macropod
[MVP - Microsoft Word]

Hi Doug Robbins; Peter Jamieson; Graham Mayor and macropod

Thanks to all of you for all the answers.

I have a mailmerge field where the field from a txt-file
display 20 digits: "09812340078901230067","zz" and the next number is
"09812340078901230068","zz" etc.

Of course I can remove the 0 (zero) from the txt-file but it is daily, takes
time and I can forget it.

I am using the number on a carrier (letter with a card) with a font 3of9 so
i can have a barcode.

Ouer mailermachine read the barcode and compare the number with the number
on the card.
Cardnumber is 19 digits: 9812340078901230067 because the card is without 0
(zero).

I hope this explains my dilemma.

I will bee back with thanks or further ask for help.


Best regards
Joergen Bondesen

Hi Joergen,

If you *really* can't change the source file, you could code your
mailmerge
template like:
{SET Data {MERGEFIELD Value}}{=INT(Data/10^5)}{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}}}}}}}}}

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/10^5)}
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 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.

If you want to group the whole lot together (eg for easier re-positioning
as
a group), you could embed them inside a QUOTE field.

Cheers

--
macropod
[MVP - Microsoft Word]

Hi NG.

I have a mailmerge field where the field from a txt-file display 20 digits:
'09812340078901230067'
I want only displayed 19 digits '9812340078901230067' because zero may
not
be displayed.
It is not posssible to remove the leading zero in the txt-file.

 

[Peter Jamieson]

You can /probably/ get the number you want quite easily by connecting to
your data source using VBA and either a .udl file, an OLEDB connection
string, and some SQL, or an ODBC DSN, an ODBC connection string, and some
SQL. However, what is feasible depends on the version of Word and thr
precise structure of your file. For example,
a. Word 2000 can't use OLEDB connections
b. if you have a large number of columns in the file, ODBC and OLEDB
connections probably won't work
c. if the numeric field is not surrounded by double-quotes, the OLEDB
method probably won't work, and you will have to edit a schema.ini file to
get ODBC to work.

But let's have a look at the ODBC approach. Let's suppose you have a file in
c:\a called mydata.txt

like

k,num
1,01234567890123456789
2,00123456789012345678

then
a. use the ODBC Administrator to ensure that you have a "Machine" DSN (user
or system) set up for the ODBC text driver. Typically there will already be
one, but if not, create one (let's call it "mydsn".
b. configure the DSN so that it looks for files in the specific directory
c:\a. Don't check "Use Current Directory"
c. In the DSN configuration box, click the Options>> box and select Define
format...
d. Select mydata.txt in the list of "Tables", check "Column Name header and
specify "CSV Delimited" (and OEM, probably.
e. Click the "Guess" columns button. "k" and "num" should appear in the box
f. select "num". The driver should have guessed Float for "num". Change the
Data Type for "num" to LongChar, and click Modify. The Width field should
grey.
g. Click OK

(An alternative to steps (c) to (g) is to create/edit a text file in c:\a
called schema.ini so it has at least the following content:

[mydata.txt]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=25
CharacterSet=OEM
Col1=k Integer
Col2=num LongChar

Then in VBA, use code along the following lines to connect to the data
source

Sub ConnectToMyText()

' Disconnect from the existing source
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
' Set up the merge type you want, e.g.
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:="DSN=mydsn;DBQ=c:\a;", _
SQLStatement:="SELECT *,iif(left(num,1)='0',mid(num,2),num) as [newnum]
FROM mydata.txt", _
Subtype:=wdMergeSubTypeWord2000

End Sub

You may need to alter the SQL to get exactly what you need.

You should only need to run the sub one time. However,
a. you may need to take account of the following article:
http://support.microsoft.com/kb/825765/en-us
b. any attempts to sort/filter the data using the usual Word facilities
won't work.

Peter Jamieson

 

[Joergen Bondesen]

Hi macropod

Sorry, my mistake. I was so intimidated by your solution.

"zz" only indicates there is more than 1 filed in the record.
Header is: "Number","Name"

--
Best regards
Joergen Bondesen

Hi Joergen,

Is the "zz" part of the same field in your text file as the 20 digits
"09812340078901230067" (eg: "09812340078901230067zz"), or are they
separate
fields (eg: "09812340078901230067" & "zz")?

Is the "zz" part of the barcode? If so, is it always "zz", or can other
letters be involved?

Cheers

--
macropod
[MVP - Microsoft Word]

Hi Doug Robbins; Peter Jamieson; Graham Mayor and macropod

Thanks to all of you for all the answers.

I have a mailmerge field where the field from a txt-file
display 20 digits: "09812340078901230067","zz" and the next number is
"09812340078901230068","zz" etc.

Of course I can remove the 0 (zero) from the txt-file but it is daily, takes
time and I can forget it.

I am using the number on a carrier (letter with a card) with a font 3of9 so
i can have a barcode.

Ouer mailermachine read the barcode and compare the number with the
number
on the card.
Cardnumber is 19 digits: 9812340078901230067 because the card is
without 0
(zero).

I hope this explains my dilemma.

I will bee back with thanks or further ask for help.


Best regards
Joergen Bondesen

Hi Joergen,

If you *really* can't change the source file, you could code your
mailmerge
template like:
{SET Data {MERGEFIELD Value}}{=INT(Data/10^5)}{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}}}}}}}}}

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/10^5)}
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 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.

If you want to group the whole lot together (eg for easier re-positioning
as
a group), you could embed them inside a QUOTE field.

Cheers

--
macropod
[MVP - Microsoft Word]


Hi NG.

I have a mailmerge field where the field from a txt-file display 20
digits:
'09812340078901230067'
I want only displayed 19 digits '9812340078901230067' because zero may
not
be displayed.
It is not posssible to remove the leading zero in the txt-file.

 

[macropod]

Hi Joergen,

I was so intimidated by your solution.

OK, but it's really not all that complex.

Now, as for the 3of9 barcode, perhaps the simplest way to convert the field
into one it to wrap it in a QUOTE field and use a '\* Charformat' switch to
apply the barcode. To do this, you'd:
.. select the set of fields that you've coded as per my previous post
.. press Ctrl-F9 to enclose them in yet another field
.. add 'QUOTE' at the beginning of the new field and '\* Charformat' at the
end, so that you end up with
(QUOTE {SET Data {MERGEFIELD Value}}{=INT(Data/10^5)}{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}
.. apply the barcode font, at the required point size, to the 'Q' in 'QUOTE'.

Cheers

--
macropod
[MVP - Microsoft Word]

Hi macropod

Sorry, my mistake. I was so intimidated by your solution.

"zz" only indicates there is more than 1 filed in the record.
Header is: "Number","Name"

--
Best regards
Joergen Bondesen

Hi Joergen,

Is the "zz" part of the same field in your text file as the 20 digits
"09812340078901230067" (eg: "09812340078901230067zz"), or are they
separate
fields (eg: "09812340078901230067" & "zz")?

Is the "zz" part of the barcode? If so, is it always "zz", or can other
letters be involved?

Cheers

--
macropod
[MVP - Microsoft Word]

Hi Doug Robbins; Peter Jamieson; Graham Mayor and macropod

Thanks to all of you for all the answers.

I have a mailmerge field where the field from a txt-file
display 20 digits: "09812340078901230067","zz" and the next number is
"09812340078901230068","zz" etc.

Of course I can remove the 0 (zero) from the txt-file but it is daily, takes
time and I can forget it.

I am using the number on a carrier (letter with a card) with a font

3of9
so

i can have a barcode.

Ouer mailermachine read the barcode and compare the number with the
number
on the card.
Cardnumber is 19 digits: 9812340078901230067 because the card is
without 0
(zero).

I hope this explains my dilemma.

I will bee back with thanks or further ask for help.


Best regards
Joergen Bondesen


Hi Joergen,

If you *really* can't change the source file, you could code your
mailmerge
template like:
{SET Data {MERGEFIELD Value}}{=INT(Data/10^5)}{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}}}}}}}}}

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/10^5)}
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 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.

If you want to group the whole lot together (eg for easier re-positioning
as
a group), you could embed them inside a QUOTE field.

Cheers

--
macropod
[MVP - Microsoft Word]


Hi NG.

I have a mailmerge field where the field from a txt-file display 20
digits:
'09812340078901230067'
I want only displayed 19 digits '9812340078901230067' because zero may
not
be displayed.
It is not posssible to remove the leading zero in the txt-file.

 

[Joergen Bondesen]

Hi macropod

I have "succes" with your code and have generated at number without leading
0.

Tomorrow I will test the barcode with my scanner.

I am trying without QUOTE... but have a copy of your advise.

But I have a slight probleme.
09810450005000600000 => 9810450005000500000 Error, because first of the
5 last digits is 0
09810450005000060000 => 9810450005000060000 OK, because first of the 5
last digits NOT is 0
09810450005000006000 => 9810450004999906000 Error, because first of the
5 last digits is 0

Digit no. 7 from left, will always be 5.
So instead of 5 [="*1????"] I must have 5 + 9 = 14 [="*1?????????????"] or
do you have a better way?

15/5 => {=INT(Data/10^5)}
6/14 => {=INT(Data/10^14)} ??


I was wondering, when I have made my field-code and want to send it in a
e-mail, how do I copy the words and brackets, because a paste copy gives me
the value?

--
Best regards
Joergen Bondesen

Hi Joergen,

I was so intimidated by your solution.

OK, but it's really not all that complex.

Now, as for the 3of9 barcode, perhaps the simplest way to convert the
field
into one it to wrap it in a QUOTE field and use a '\* Charformat' switch
to
apply the barcode. To do this, you'd:
. select the set of fields that you've coded as per my previous post
. press Ctrl-F9 to enclose them in yet another field
. add 'QUOTE' at the beginning of the new field and '\* Charformat' at the
end, so that you end up with
(QUOTE {SET Data {MERGEFIELD Value}}{=INT(Data/10^5)}{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}
. apply the barcode font, at the required point size, to the 'Q' in
'QUOTE'.

Cheers

--
macropod
[MVP - Microsoft Word]

Hi macropod

Sorry, my mistake. I was so intimidated by your solution.

"zz" only indicates there is more than 1 filed in the record.
Header is: "Number","Name"

--
Best regards
Joergen Bondesen

Hi Joergen,

Is the "zz" part of the same field in your text file as the 20 digits
"09812340078901230067" (eg: "09812340078901230067zz"), or are they
separate
fields (eg: "09812340078901230067" & "zz")?

Is the "zz" part of the barcode? If so, is it always "zz", or can other
letters be involved?

Cheers

--
macropod
[MVP - Microsoft Word]


Hi Doug Robbins; Peter Jamieson; Graham Mayor and macropod

Thanks to all of you for all the answers.

I have a mailmerge field where the field from a txt-file
display 20 digits: "09812340078901230067","zz" and the next number is
"09812340078901230068","zz" etc.

Of course I can remove the 0 (zero) from the txt-file but it is daily,
takes
time and I can forget it.

I am using the number on a carrier (letter with a card) with a font 3of9
so
i can have a barcode.

Ouer mailermachine read the barcode and compare the number with the
number
on the card.
Cardnumber is 19 digits: 9812340078901230067 because the card is
without
0
(zero).

I hope this explains my dilemma.

I will bee back with thanks or further ask for help.


Best regards
Joergen Bondesen


Hi Joergen,

If you *really* can't change the source file, you could code your
mailmerge
template like:
{SET Data {MERGEFIELD Value}}{=INT(Data/10^5)}{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}}}}}}}}}

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/10^5)}
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 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.

If you want to group the whole lot together (eg for easier
re-positioning
as
a group), you could embed them inside a QUOTE field.

Cheers

--
macropod
[MVP - Microsoft Word]


Hi NG.

I have a mailmerge field where the field from a txt-file display 20
digits:
'09812340078901230067'
I want only displayed 19 digits '9812340078901230067' because zero may
not
be displayed.
It is not posssible to remove the leading zero in the txt-file.

 

[Joergen Bondesen]

Hi Peter

Thanks.
I will have a closer look at your advise later this month, because I am not
so experienced.

--
Best regards
Joergen Bondesen

You can /probably/ get the number you want quite easily by connecting to
your data source using VBA and either a .udl file, an OLEDB connection
string, and some SQL, or an ODBC DSN, an ODBC connection string, and some
SQL. However, what is feasible depends on the version of Word and thr
precise structure of your file. For example,
a. Word 2000 can't use OLEDB connections
b. if you have a large number of columns in the file, ODBC and OLEDB
connections probably won't work
c. if the numeric field is not surrounded by double-quotes, the OLEDB
method probably won't work, and you will have to edit a schema.ini file to
get ODBC to work.

But let's have a look at the ODBC approach. Let's suppose you have a file
in c:\a called mydata.txt

like

k,num
1,01234567890123456789
2,00123456789012345678

then
a. use the ODBC Administrator to ensure that you have a "Machine" DSN
(user or system) set up for the ODBC text driver. Typically there will
already be one, but if not, create one (let's call it "mydsn".
b. configure the DSN so that it looks for files in the specific directory
c:\a. Don't check "Use Current Directory"
c. In the DSN configuration box, click the Options>> box and select Define
format...
d. Select mydata.txt in the list of "Tables", check "Column Name header
and specify "CSV Delimited" (and OEM, probably.
e. Click the "Guess" columns button. "k" and "num" should appear in the
box
f. select "num". The driver should have guessed Float for "num". Change
the Data Type for "num" to LongChar, and click Modify. The Width field
should grey.
g. Click OK

(An alternative to steps (c) to (g) is to create/edit a text file in c:\a
called schema.ini so it has at least the following content:

[mydata.txt]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=25
CharacterSet=OEM
Col1=k Integer
Col2=num LongChar

Then in VBA, use code along the following lines to connect to the data
source

Sub ConnectToMyText()

' Disconnect from the existing source
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
' Set up the merge type you want, e.g.
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:="DSN=mydsn;DBQ=c:\a;", _
SQLStatement:="SELECT *,iif(left(num,1)='0',mid(num,2),num) as [newnum]
FROM mydata.txt", _
Subtype:=wdMergeSubTypeWord2000

End Sub

You may need to alter the SQL to get exactly what you need.

You should only need to run the sub one time. However,
a. you may need to take account of the following article:
http://support.microsoft.com/kb/825765/en-us
b. any attempts to sort/filter the data using the usual Word facilities
won't work.

Peter Jamieson

Yes.

Which version(s) of Word are you using?

What is the specific reason that you cannot remove the leading 0 in the
text file (which may be the best way to do this)? Is it because you need
to avoid creating new files in the local file system, or do you just mean
that you have no control over how the file is created in the first place?

What is the exact format of the text file? Is it comma-delimited?
tab-delimited? Some other delimiter? And is the number enclosed in quotes
or not, e.g.

is it

k,num
1,"01234567890123456789"

k,num
1,01234567890123456789

or what?

Are all the numbers 20 digits long? Do you only ever need to strip a
single leading zero, or if there are more leading zeros, do you need to
strip them all?

If it's only one leading zero, would it be enough to overwrite the 0 by a
white-coloured zero, e.g. using something like

{ MERGEFIELD num }{ ADVANCE \l 130 }0{ ADVANCE \r 140 }

(You would need to colour that "0" white, and adjust the two values in
the ADVANCE fields, but because digits are always fixed width this should
"work" as long as you can use ADVANCE to move exactly the correct
distance leftwards. Tacky, but it might be the simplest approach)

Peter Jamieson

 

[macropod]

Hi Joergen,

Seems that in trying to keep things simple (HA!) I was a bit too aggressive
with the INT part of the formula. Instead of =INT(Data/10^5) you need
=INT(Data/100000). For some peculiar reason, 10^5 isn't being evaluated as
100000!

Here's the complete coding, including the QUOTE field wrapper I mentioned.
An advantage of using the QUOTE field wrapper is that you can have paragraph
marks separating the nested IF fields, to keep the layout just that bit more
comprehensible, without any effect on the output.

{QUOTE{SET Data {MERGEFIELD Value}}{=INT(Data/100000)}

{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}

Cheers

--
macropod
[MVP - Microsoft Word]

Hi macropod

I have "succes" with your code and have generated at number without leading
0.

Tomorrow I will test the barcode with my scanner.

I am trying without QUOTE... but have a copy of your advise.

But I have a slight probleme.
09810450005000600000 => 9810450005000500000 Error, because first of the
5 last digits is 0
09810450005000060000 => 9810450005000060000 OK, because first of the 5
last digits NOT is 0
09810450005000006000 => 9810450004999906000 Error, because first of the
5 last digits is 0

Digit no. 7 from left, will always be 5.
So instead of 5 [="*1????"] I must have 5 + 9 = 14 [="*1?????????????"] or
do you have a better way?

15/5 => {=INT(Data/10^5)}
6/14 => {=INT(Data/10^14)} ??


I was wondering, when I have made my field-code and want to send it in a
e-mail, how do I copy the words and brackets, because a paste copy gives me
the value?

--
Best regards
Joergen Bondesen

Hi Joergen,

I was so intimidated by your solution.

OK, but it's really not all that complex.

Now, as for the 3of9 barcode, perhaps the simplest way to convert the
field
into one it to wrap it in a QUOTE field and use a '\* Charformat' switch
to
apply the barcode. To do this, you'd:
. select the set of fields that you've coded as per my previous post
. press Ctrl-F9 to enclose them in yet another field
. add 'QUOTE' at the beginning of the new field and '\* Charformat' at the
end, so that you end up with
(QUOTE {SET Data {MERGEFIELD Value}}{=INT(Data/10^5)}{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}
. apply the barcode font, at the required point size, to the 'Q' in
'QUOTE'.

Cheers

--
macropod
[MVP - Microsoft Word]

Hi macropod

Sorry, my mistake. I was so intimidated by your solution.

"zz" only indicates there is more than 1 filed in the record.
Header is: "Number","Name"

--
Best regards
Joergen Bondesen


Hi Joergen,

Is the "zz" part of the same field in your text file as the 20 digits
"09812340078901230067" (eg: "09812340078901230067zz"), or are they
separate
fields (eg: "09812340078901230067" & "zz")?

Is the "zz" part of the barcode? If so, is it always "zz", or can other
letters be involved?

Cheers

--
macropod
[MVP - Microsoft Word]


Hi Doug Robbins; Peter Jamieson; Graham Mayor and macropod

Thanks to all of you for all the answers.

I have a mailmerge field where the field from a txt-file
display 20 digits: "09812340078901230067","zz" and the next number is
"09812340078901230068","zz" etc.

Of course I can remove the 0 (zero) from the txt-file but it is daily,
takes
time and I can forget it.

I am using the number on a carrier (letter with a card) with a font 3of9
so
i can have a barcode.

Ouer mailermachine read the barcode and compare the number with the
number
on the card.
Cardnumber is 19 digits: 9812340078901230067 because the card is
without
0
(zero).

I hope this explains my dilemma.

I will bee back with thanks or further ask for help.


Best regards
Joergen Bondesen


Hi Joergen,

If you *really* can't change the source file, you could code your
mailmerge
template like:
{SET Data {MERGEFIELD Value}}{=INT(Data/10^5)}{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}}}}}}}}}

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/10^5)}
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 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.

If you want to group the whole lot together (eg for easier
re-positioning
as
a group), you could embed them inside a QUOTE field.

Cheers

--
macropod
[MVP - Microsoft Word]


Hi NG.

I have a mailmerge field where the field from a txt-file display 20
digits:
'09812340078901230067'
I want only displayed 19 digits '9812340078901230067' because

zero
may

not
be displayed.
It is not posssible to remove the leading zero in the txt-file.

 

[Joergen Bondesen]

Hi macropod

BINGO.

Today I have tested the barcode and everything is ok.
Thanks for your effort to make i possible for me and my colleagues to save a
lot of time.

 

[macropod]

You're welcome.