Excel to Word mailmerge help

YorkshireTraveller

Can someone help with an Excel to Word mail merge problem I’m having?

When I print address labels for our car club magazine I also print the membership number at the top of each label importing this number from the <<membership no>> field in the Excel spreadsheet. This works fine for simple numeric membership numbers.

However, for several categories of membership I use the format of letter then number e.g. S01 and C02. These do not get imported correctly into the mail merge and show as <<0>> (zero).

I have tried to changing the format of the Excel sheet membership number cells so that they are in general format rather than specifically numeric but this has not helped.

I would be most grateful for any suggestions as to what I’m dong wrong and how to correct this.

Many thanks.

Marty999

Have you tried entering the prefix letter in the Word doc, and then merging the variable number with it from your worksheet?

Jivesinger

I've not much experience of mail merges, but I've had this problem in other scenarios when trying to get data out of Excel. In the problems I've had, the software looks at the first few rows in the spreadsheet and 'guesses' that the data type in that column is numeric.

A slightly clunky way round it would be to try inserting a dummy row at the top of your spreadsheet, with an alphabetic membership number (eg. a membership number of 'DUMMY'). It would waste a label though.

You might also try applying the 'Text' format to that column rather than 'General' but I'm not sure whether that would work.

YorkshireTraveller

Marty999 - I'm merging dirctly to labels (albeit in word format)

Jivesinger - I have tried text format but this doesn't help

cookie365

In what way does text format 'not help'?

Why not add an extra column in your spreadsheet with a formula to convert the membership number to text. Plenty of step by steps on the internet.

Then merge that field as text onto the label in exactly the same way as you merge the text address fields.

YorkshireTraveller

cookie365 wrote: »

In what way does text format 'not help'?

Why not add an extra column in your spreadsheet with a formula to convert the membership number to text. Plenty of step by steps on the internet.

Then merge that field as text onto the label in exactly the same way as you merge the text address fields.

I have tried formating the column/cells as text but the membership numbers print correctly if numeric but as '0' if alpha numeric.

Jivesinger

cookie365 wrote: »

In what way does text format 'not help'?

Have to admit I have just tried this (with Office 2003) and it indeed doesn't help. The mail merge ignores the format.

However this does work for me (as would my DUMMY row):

cookie365 wrote: »

Why not add an extra column in your spreadsheet with a formula to convert the membership number to text. Plenty of step by steps on the internet.

Then merge that field as text onto the label in exactly the same way as you merge the text address fields.

I added an extra column with the formula

=""&A2

(Assuming the membership numbers start from cell A2, put this formula in row 2 on the new column, and copy it down) and it does the job.

YorkshireTraveller

Jivesinger - I've tried your suggestion of a DUMMY entry and this works perfectly (I'm also using Office 2003).

Both numeric and alpha numeric membership numbers now print on the address labels.

There's no waste of a label as I simply uncheck this entry before printing.

I wonder why this happens?