Mail merge pulling incorrect data from Excel data source

Posted on 2012-08-16
Last Modified: 2012-08-16
I have a mail merge document that we run every week.  There is a CSV file that gets dumped from our ERP system once a week.  I open the CSV in Excel, format some of the fields and remove some of the data, resave it to a different location (My Documents) as an XLS.  Then in the mail merge document, I choose "Select a different list" for the data, select the new XLS file I just saved, and it recognizes and pulls in the all of the data.  However, one field is an item number field.  Most item numbers are just 5-8 digit numbers but some of them have a a size extension, which shows as a number with a "-XL".  When these pull over to the Word doc, for some reason it is turning them into zeros (0).  This process is repeated weekly for the past 2 or 3 years.  If I pull in the data from last week's file, it works fine.  I have recreated this week's file several times in different formats and it continues to do this.  Does anyone have any idea why the mail merge would change any values that have a dash in them to a zero?

Any help is greatly appreciated!

Question by:npc_caseym
    LVL 1

    Accepted Solution

    Try this and see if it helps.

    select the entire column, go to the Data menu and use the Text to Columns tool. Set the column specifically to be "TEXT" (not general or numeric). SAVE the change, link the
     data back into the mail merge document, and test.

    Author Closing Comment

    Wow...that actually worked!  I have been banging my head off the wall for 2 days trying to figure out a solution for this.  Thanks so much babyhuey!

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
    A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now