 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Mailmerge and Fax / December 2006Remove the leading zero from mergefield .
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. ----- Best Regards from Joergen Bondesen Put the mergefield in a formula that mulitplies it by 1 { = { MERGEFIELD fieldname } * 1 } You must use Ctrl+F9 to insert the field delimiters { }  SignatureHope 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 > Hi NG. > [quoted text clipped - 7 lines] > Best Regards from > 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? SignatureBest Regards Joergen Bondesen > Put the mergefield in a formula that mulitplies it by 1 > [quoted text clipped - 13 lines] >> Best Regards from >> Joergen Bondesen > 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 > Hi Dough > [quoted text clipped - 23 lines] >>> Best Regards from >>> Joergen Bondesen 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' Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> >> Does Word have a limitation of 14 / 15 digits? > [quoted text clipped - 76 lines] >>>> Best Regards from >>>> 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 >> Does Word have a limitation of 14 / 15 digits? > [quoted text clipped - 64 lines] >>>> Best Regards from >>>> Joergen Bondesen Hi Peter Thanks. I will have a closer look at your advise later this month, because I am not so experienced. SignatureBest 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 [quoted text clipped - 141 lines] >>>>> Best Regards from >>>>> 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 Signaturemacropod [MVP - Microsoft Word] > Hi NG. > [quoted text clipped - 7 lines] > Best Regards from > 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, > [quoted text clipped - 103 lines] >> Best Regards from >> 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 Signaturemacropod [MVP - Microsoft Word] > Hi Doug Robbins; Peter Jamieson; Graham Mayor and macropod > [quoted text clipped - 129 lines] > >> Best Regards from > >> Joergen Bondesen Hi macropod Sorry, my mistake. I was so intimidated by your solution. 8-) "zz" only indicates there is more than 1 filed in the record. Header is: "Number","Name" SignatureBest regards Joergen Bondesen > Hi Joergen, > [quoted text clipped - 165 lines] >> >> Best Regards from >> >> 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 Signaturemacropod [MVP - Microsoft Word] > Hi macropod > [quoted text clipped - 172 lines] > >> >> Best Regards from > >> >> 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? SignatureBest regards Joergen Bondesen > Hi Joergen, > [quoted text clipped - 235 lines] >> >> >> Best Regards from >> >> >> Joergen Bondesen 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 Signaturemacropod [MVP - Microsoft Word] > Hi macropod > [quoted text clipped - 263 lines] > >> >> >> Best Regards from > >> >> >> 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. SignatureBest regards Joergen Bondesen You're welcome. Signaturemacropod [MVP - Microsoft Word] > Hi macropod > [quoted text clipped - 3 lines] > Thanks for your effort to make i possible for me and my colleagues to save a > lot of time. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.