Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2016
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to remove characters from merge field

    Hi,

    I'm trying to remove the first and last characters from a merge field. Thanks in advance.

    *10028481005* to 10028481005

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    There is no practical way of doing that via field coding - which is what you'd need to di this as part of a mailmerge. You will need to either modify the data or edit the output post-merge.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    If you are comfortable with vba and sql then it is simple enough to do this without modifying the dataset.

    Basically, a mail merge document includes a sql query to the datasource and this query can be edited to truncate a field.

    With the mail merge document open, go to VBA and run the following code in the immediate window to find out what the current query is...
    ? ActiveDocument.MailMerge.DataSource.QueryString

    This will likely return something like
    SELECT * FROM `Sheet1$`

    If you configured your merge data a little bit to exclude some of the fields you would see something more useful such as
    SELECT Name, ID, Text FROM `Sheet1$`

    In either case, you can then use this information to run a line of VBA which would modify the SQL query
    ActiveDocument.MailMerge.DataSource.QueryString = "SELECT Name, Mid(ID,2,Len(ID)-2) as ID, Text FROM `Sheet1$`"
    Andrew Lockton, Chrysalis Design, Melbourne Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •