Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Laurel Laurel is offline
external usenet poster
 
Posts: 8
Default Truncating in Mail Merge

Hi All,
I'd like to print just the last 4 numbers of an account number in a mail
merge letter (front fill the rest with asterisks). It seems like it should be
just as simple as the RIGHT function in Excel but by necessity my source
document has be a CSV text document so the function has to take place in
Word. Is this possible?

Thanks so much!
Laurel
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default Truncating in Mail Merge

Unfortunately there is no RIGHT function associated with Word fields. If the
field contains a number (rather than numbers and letters) then you could
cobble something together along the lines of

{ SET AB { =({ Mergefield fieldname } / 10000 ) \# "0.0000"} } ***{ =({
AB } - { =INT({ AB }) }) * 10000 }

with the number of asterisks representing the missing leading numbers, but
if it is not a simple number, you will have to edit the data file.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


Laurel wrote:
Hi All,
I'd like to print just the last 4 numbers of an account number in a
mail merge letter (front fill the rest with asterisks). It seems like
it should be just as simple as the RIGHT function in Excel but by
necessity my source document has be a CSV text document so the
function has to take place in Word. Is this possible?

Thanks so much!
Laurel



  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Truncating in Mail Merge


It seems like it should be
just as simple as the RIGHT function in Excel


It does, but unfortunately it isn't.

If the account number is a whole number (or in some case, contains a whole
number) not more than around 14 or so digits long, you can use

{ =mod({ MERGEFIELD youraccountfield },10000) } and prefix any asterixes you
need.

All the {} need to be the special field code braces that you can insert
using ctrl-F9.

If the account number is more complex there are two things you can do other
than transform your data source into another format:
a. use a DATABASE field - nasty, but see http://tips.pjmsn.me.uk/t0004.htm
b. use Word VBA to issue a SQL query to get the data from the .csv file,
and put the necessary right() function in that query. That isn't
straightforward either, but it can usually be done as long as the .csv can
be read by the Jet ODBC driver/OLE DB provider and doesn't have more than
255 fields.

Peter Jamieson


"Laurel" wrote in message
...
Hi All,
I'd like to print just the last 4 numbers of an account number in a mail
merge letter (front fill the rest with asterisks). It seems like it should
be
just as simple as the RIGHT function in Excel but by necessity my source
document has be a CSV text document so the function has to take place in
Word. Is this possible?

Thanks so much!
Laurel


  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Laurel Laurel is offline
external usenet poster
 
Posts: 8
Default Truncating in Mail Merge

Thank you so much to you and Graham. I'm having trouble getting it to work
without syntax errors. I don't have a lot of experience with mail merge. But
while I was trying to figure it out, I came across a numeric picture switch
that drops digits to the left of the "x" placeholder. I'm still unable to
type without getting syntax errors but theoretically would this work?

{ = 111053 + 111439 \# x## } displays "492". (example in documentation)
{ = «Cardholder_Number»\# x### } (how it would maybe work for me?)

Also, I'm still unable to key this into my document so that it works. I can
get the brackets using Ctrl-F9 but everything I type inside gets a syntax
error.

Thank you so much. You guys are great!

"Peter Jamieson" wrote:


It seems like it should be
just as simple as the RIGHT function in Excel


It does, but unfortunately it isn't.

If the account number is a whole number (or in some case, contains a whole
number) not more than around 14 or so digits long, you can use

{ =mod({ MERGEFIELD youraccountfield },10000) } and prefix any asterixes you
need.

All the {} need to be the special field code braces that you can insert
using ctrl-F9.

If the account number is more complex there are two things you can do other
than transform your data source into another format:
a. use a DATABASE field - nasty, but see http://tips.pjmsn.me.uk/t0004.htm
b. use Word VBA to issue a SQL query to get the data from the .csv file,
and put the necessary right() function in that query. That isn't
straightforward either, but it can usually be done as long as the .csv can
be read by the Jet ODBC driver/OLE DB provider and doesn't have more than
255 fields.

Peter Jamieson


"Laurel" wrote in message
...
Hi All,
I'd like to print just the last 4 numbers of an account number in a mail
merge letter (front fill the rest with asterisks). It seems like it should
be
just as simple as the RIGHT function in Excel but by necessity my source
document has be a CSV text document so the function has to take place in
Word. Is this possible?

Thanks so much!
Laurel


  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Truncating in Mail Merge

HI Laurel,

You're on the right track, but let's start from scratch:

1. To do it the way you propose:

Select the point where you want to enter the field in your document.

Press ctrl F9 to get

{ }

Inside that, type

= \# x###

to give you

{ = \# x### }

Click just before the backslash and use ctrl-F9 to insert another {}, to
give you

{ = { } \# x### }


then between those new {}, type

MERGEFIELD Cardholder_Number

to give you

{ = { MERGEFIELD Cardholder_Number
} \# x### }

2. To do it the way I suggested,...

Select the point where you want to enter the field in your document.

Press ctrl F9 to get

{ }

Inside that, type

=mod(,10000)

to give you

{ =mod(,10000) }

Click just before the comma and use ctrl-F9 to insert another {}, to give
you

{ =mod({ },10000) }

then between those new {}, type

MERGEFIELD "the name of your account field"

to give you

{ =mod({ MERGEFIELD "the name of your account field"
},10000) }

3. That's probably enough for you to follow Graham's suggestion as well.

Peter Jamieson


"Laurel" wrote in message
...
Thank you so much to you and Graham. I'm having trouble getting it to work
without syntax errors. I don't have a lot of experience with mail merge.
But
while I was trying to figure it out, I came across a numeric picture
switch
that drops digits to the left of the "x" placeholder. I'm still unable to
type without getting syntax errors but theoretically would this work?

{ = 111053 + 111439 \# x## } displays "492". (example in documentation)
{ = «Cardholder_Number»\# x### } (how it would maybe work for me?)

Also, I'm still unable to key this into my document so that it works. I
can
get the brackets using Ctrl-F9 but everything I type inside gets a syntax
error.

Thank you so much. You guys are great!

"Peter Jamieson" wrote:


It seems like it should be
just as simple as the RIGHT function in Excel


It does, but unfortunately it isn't.

If the account number is a whole number (or in some case, contains a
whole
number) not more than around 14 or so digits long, you can use

{ =mod({ MERGEFIELD youraccountfield },10000) } and prefix any asterixes
you
need.

All the {} need to be the special field code braces that you can insert
using ctrl-F9.

If the account number is more complex there are two things you can do
other
than transform your data source into another format:
a. use a DATABASE field - nasty, but see
http://tips.pjmsn.me.uk/t0004.htm
b. use Word VBA to issue a SQL query to get the data from the .csv file,
and put the necessary right() function in that query. That isn't
straightforward either, but it can usually be done as long as the .csv
can
be read by the Jet ODBC driver/OLE DB provider and doesn't have more than
255 fields.

Peter Jamieson


"Laurel" wrote in message
...
Hi All,
I'd like to print just the last 4 numbers of an account number in a
mail
merge letter (front fill the rest with asterisks). It seems like it
should
be
just as simple as the RIGHT function in Excel but by necessity my
source
document has be a CSV text document so the function has to take place
in
Word. Is this possible?

Thanks so much!
Laurel





  #6   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default Truncating in Mail Merge

I think yours looks simpler

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


Peter Jamieson wrote:

3. That's probably enough for you to follow Graham's suggestion as
well.
Peter Jamieson




  #7   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Laurel Laurel is offline
external usenet poster
 
Posts: 8
Default Truncating in Mail Merge

{ MERGEFIELD «Cardholder_Number»\# x### } works just like the RIGHT function
in Excel and displays the last 4 digits of the cardholder number in my
letter. Simple and no math!

Thanks for your help and suggestions.
Laurel

"Laurel" wrote:

Thank you so much to you and Graham. I'm having trouble getting it to work
without syntax errors. I don't have a lot of experience with mail merge. But
while I was trying to figure it out, I came across a numeric picture switch
that drops digits to the left of the "x" placeholder. I'm still unable to
type without getting syntax errors but theoretically would this work?

{ = 111053 + 111439 \# x## } displays "492". (example in documentation)
{ = «Cardholder_Number»\# x### } (how it would maybe work for me?)

Also, I'm still unable to key this into my document so that it works. I can
get the brackets using Ctrl-F9 but everything I type inside gets a syntax
error.

Thank you so much. You guys are great!

"Peter Jamieson" wrote:


It seems like it should be
just as simple as the RIGHT function in Excel


It does, but unfortunately it isn't.

If the account number is a whole number (or in some case, contains a whole
number) not more than around 14 or so digits long, you can use

{ =mod({ MERGEFIELD youraccountfield },10000) } and prefix any asterixes you
need.

All the {} need to be the special field code braces that you can insert
using ctrl-F9.

If the account number is more complex there are two things you can do other
than transform your data source into another format:
a. use a DATABASE field - nasty, but see http://tips.pjmsn.me.uk/t0004.htm
b. use Word VBA to issue a SQL query to get the data from the .csv file,
and put the necessary right() function in that query. That isn't
straightforward either, but it can usually be done as long as the .csv can
be read by the Jet ODBC driver/OLE DB provider and doesn't have more than
255 fields.

Peter Jamieson


"Laurel" wrote in message
...
Hi All,
I'd like to print just the last 4 numbers of an account number in a mail
merge letter (front fill the rest with asterisks). It seems like it should
be
just as simple as the RIGHT function in Excel but by necessity my source
document has be a CSV text document so the function has to take place in
Word. Is this possible?

Thanks so much!
Laurel


  #8   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default Truncating in Mail Merge

Of course it does! I wouldn't mind but I did a web page about this

http://www.gmayor.com/formatting_word_fields.htm


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Laurel wrote:
{ MERGEFIELD «Cardholder_Number»\# x### } works just like the RIGHT
function in Excel and displays the last 4 digits of the cardholder
number in my letter. Simple and no math!

Thanks for your help and suggestions.
Laurel

"Laurel" wrote:

Thank you so much to you and Graham. I'm having trouble getting it
to work without syntax errors. I don't have a lot of experience with
mail merge. But while I was trying to figure it out, I came across a
numeric picture switch that drops digits to the left of the "x"
placeholder. I'm still unable to type without getting syntax errors
but theoretically would this work?

{ = 111053 + 111439 \# x## } displays "492". (example in
documentation) { = «Cardholder_Number»\# x### } (how it would maybe
work for me?)

Also, I'm still unable to key this into my document so that it
works. I can get the brackets using Ctrl-F9 but everything I type
inside gets a syntax error.

Thank you so much. You guys are great!

"Peter Jamieson" wrote:


It seems like it should be
just as simple as the RIGHT function in Excel


It does, but unfortunately it isn't.

If the account number is a whole number (or in some case, contains
a whole number) not more than around 14 or so digits long, you can
use

{ =mod({ MERGEFIELD youraccountfield },10000) } and prefix any
asterixes you need.

All the {} need to be the special field code braces that you can
insert using ctrl-F9.

If the account number is more complex there are two things you can
do other than transform your data source into another format:
a. use a DATABASE field - nasty, but see
http://tips.pjmsn.me.uk/t0004.htm b. use Word VBA to issue a SQL
query to get the data from the .csv file, and put the necessary
right() function in that query. That isn't straightforward either,
but it can usually be done as long as the .csv can be read by the
Jet ODBC driver/OLE DB provider and doesn't have more than 255
fields.

Peter Jamieson


"Laurel" wrote in message
...
Hi All,
I'd like to print just the last 4 numbers of an account number in
a mail merge letter (front fill the rest with asterisks). It seems
like it should be
just as simple as the RIGHT function in Excel but by necessity my
source document has be a CSV text document so the function has to
take place in Word. Is this possible?

Thanks so much!
Laurel



  #9   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Truncating in Mail Merge

Unfortunately mine would also need something like \#0000 to cope with
leading zeros in the last four digits so Laurel's wins AFAICS!

Peter Jamieson
"Graham Mayor" wrote in message
...
I think yours looks simpler

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


Peter Jamieson wrote:

3. That's probably enough for you to follow Graham's suggestion as
well.
Peter Jamieson





Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
More Mail Merge truncating cheshirecat Mailmerge 8 May 22nd 09 11:27 PM
Truncating fields [email protected] Page Layout 1 December 13th 05 08:28 AM
How to stop Word from truncating data in an mail merge field? Hope Hare Mailmerge 1 September 8th 05 07:07 PM
Why is the WORD mail merge truncating information? KristineB Mailmerge 3 June 14th 05 09:31 AM
Truncating leading zeroes JM Microsoft Word Help 3 December 2nd 04 05:16 PM


All times are GMT +1. The time now is 07:01 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"