Tech: Mailing Labels Word 2003

Subject: Tech: Mailing Labels Word 2003
From: SPB-Cooperator
Date: 24 Feb 11 - 12:25 PM

I am working with a client who has Word 2003, trying to get a set of mailing labels from a mailing list in an excel spreadsheet.

The problem is that Word is insisting on putting the Postcode on the same line as the last line of the address.

How can I get word to put the postcode on a line on its own? There are too many addresses to manually insert 'enter' on each label.

thanks (in anticipation).

Subject: RE: Tech: Mailing Labels Word 2003
From: alex s
Date: 24 Feb 11 - 12:30 PM

not really a music-related question, is it...

Subject: RE: Tech: Mailing Labels Word 2003
From: SPB-Cooperator
Date: 24 Feb 11 - 12:33 PM

no, it is a technical question - hoping that someone in the folk community is cleverer than me

Subject: RE: Tech: Mailing Labels Word 2003
From: My guru always said
Date: 24 Feb 11 - 01:00 PM

Set up the label as a template template under 'letters & mailings' and point it towards the excel spreadsheet as a database via a 'mail-merge'.

Within the label, create each line as a 'field' from the column headers within the spreadsheet. You can put these on each line as you would normally see on an envelope as long as you set it up that way.

If this is too simplistic I apologise. If you need more step by step instructions, please PM me. I'm sure there are very few other Catters who are interested....

If this is sufficient, good luck!!

Subject: RE: Tech: Mailing Labels Word 2003
From: Bonzo3legs
Date: 24 Feb 11 - 01:29 PM

It's a nightmare!!

Subject: RE: Tech: Mailing Labels Word 2003
From: Amos
Date: 24 Feb 11 - 04:46 PM

No, it's a feature!!! From Microsoft!!!!

Subject: RE: Tech: Mailing Labels Word 2003
From: My guru always said
Date: 24 Feb 11 - 05:29 PM

Too many templates, sorry!

Of course, that's all supposing that the Postcode has been set up in a separate column in the spreadsheet....

Subject: RE: Tech: Mailing Labels Word 2003
From: JohnInKansas
Date: 24 Feb 11 - 05:40 PM

The US Postal System Specification says that the "Postal Code" (ZIP Code) is supposed to be on the same line with the City & State.

So where are you? - And is the Word 2003 Regionalized for the same place?

Have you looked in Word 2003 HELP? (Search in help for "Mail Merge." Unlike Word 2010 sometimes earlier versions did have useful information.)

John

Subject: RE: Tech: Mailing Labels Word 2003
From: Greg F.
Date: 24 Feb 11 - 05:58 PM

Unlike Microsoft products, this actually works. Not sure if it can import from Excel.

http://www.avery.com/avery/en_us/Templates-&-Software/Software/Avery-Wizard-for-Microsoft-Office.htm

Subject: RE: Tech: Mailing Labels Word 2003
From: Joe Offer
Date: 24 Feb 11 - 06:32 PM

Like Greg F., I would recommend the Avery Wizard. It does a good job adapting Office to all the various Avery label sizes, and it's fun to use - and it's free.

-Joe-

P.S. I moved this one down to the non-music section. It was certainly appropriate to label this thread "Tech" and let us moderators move it if it's not a music or Mudcat-related tech issue. Same with non-music obituaries. If they're non-music, we move 'em, so there's no need to worry about it.

Subject: RE: Tech: Mailing Labels Word 2003
From: SPB-Cooperator
Date: 25 Feb 11 - 04:03 PM

Thanks for the help. I forgot that one doesn't have to use the label wizard. So long since I last did a label run!

I'll look at the Avery Application too and let you all know how I get on. For info - the client is a charity with a contact list of nearly 2000 service users!

Also, hopefully the info might be useful for any performers/clubs who do regular mail-outs.

Subject: RE: Tech: Mailing Labels Word 2003
From: JohnInKansas
Date: 25 Feb 11 - 05:16 PM

While you haven't really said how the address list is set up, speculatively:

2000 names/addresses in the Excel file implies only 2000 rows.

In order to specify where the "post code" goes separately from where the address goes on the labels, the two need to be in separate columns (with separate data item labels at the top of each column) in the spread sheet. If they insist on being on the same line, it's likely that both are in the same cell.

Select the column that contains them both, copy and paste into Word.

It should paste as a table.

Convert the table to text (Alt-A, V, B) - (or in Word 2003 you can use the Table menu, but I've never found it in Word 2010) - and use Word's Edit|Replace (Alt-E, E) to put a tab between address and post code. (cleverness may be required)

When you convert the text back into a table (Alt-A, V, X), you should get two columns in the table, with the break where the tab was.

Copy the table and paste back into a copy of the Excel file¹ so that you have two new columns, (1)Address and (2)post code. Put labels on the new columns. Delete the old address+post column.

¹ NEVER make changes to your original, unless you're ABSOLUTELY certain what you're doing is gonna work.

In Word, you'll need to know that you put a tab into either the Find What box or the Replace With box by typing ^t.

The precise way you'd proceed to split the data record would depend on the exact way the address list is set up, but for a US style address in the form:

City, State ZIP

I'd replace all the [space] characters with ^t (a tab) which would result in

City,[tab]State[tab]ZIP

followed by replace all [,^t] by ,[space], to get:

City, State[tab]ZIP

Word 2003, with typical memory, should easily handle 2000 lines of text to make the replacements. It might bomb due to memory limits somewhere around 500 pages (25 lines per page = 12,000 lines) with "dense" replacements, although I've worked lots of global replace tasks in 1200 page docs.

The conversion back and forth between table and text may take a while but that's not really a very large selection to flip back and forth unless memory is very limited.

Once you have separate "labels" on the Address and Post Code, you should be able to set up the label printing fairly easily, in whatever manner you usually prefer.

If you can't figure out a way to replace one specific space without replacing more than one, the table you paste back into Excel may have "too many" columns; but you can "merge columns" in Excel to combine the ones that belong together, as long as they're adjacent.

Of course, other methods already suggested my work better for you, or your data may require something different.

John

Subject: RE: Tech: Dates in Mailmerge
From: SPB-Cooperator
Date: 17 Mar 11 - 11:58 AM

For same charity I have started to mailmerge excel spreadsheets into word documents to generate invoices.

Now...

The date fields in the spreadsheet are UK format
The international settings in control panel are for UK

But, when I run the merge, WORD kindly decides that I should have the dates in US format.

Please can anyone tell me what I am doing wrong and how I can get round it. This time I am working in Word 2007. Te help is not very helpful,

Subject: RE: Tech: Mailing Labels Word 2003
From: GUEST,999
Date: 17 Mar 11 - 12:09 PM

From: Bonzo3legs
Date: 24 Feb 11 - 01:29 PM

It's a nightmare!!


From: Amos
Date: 24 Feb 11 - 04:46 PM

No, it's a feature!!! From Microsoft!!!!



My first good chuckle of the day. Thank you both.

Subject: RE: Tech: Mailing Labels Word 2003
From: Arthur_itus
Date: 17 Mar 11 - 12:17 PM

SPB I have sent you a PM

Subject: RE: Tech: Mailing Labels Word 2003
From: SPB-Cooperator
Date: 17 Mar 11 - 06:32 PM

The real 'chuckle' is that a multi-billion corporation that is intent of dominating the world market is incapable of conforming to the standards of the countries they are selling to.

It really pisses me off as I have to spend hours on trial and error getting round what is basically Microsoft's laziness.

Subject: RE: Tech: Mailing Labels Word 2003
From: JohnInKansas
Date: 18 Mar 11 - 12:29 AM

Microsoft does face the persistent problem that users refuse to RTFM.

My recollection is that I never had any problems with mail merge or labels in Word 2003, because all of the necessary instructions are in Help files on any machine that has Word 2003 installed.

YOU have to prepare the merge-field to determine what is included in the address, and YOU must prepare the database so that the separate elements of the address are each separately identified in the database.

Excel is NOT the preferred database for mailing lists/address labels, but will work if set up properly.

The situation is somewhat different for Office 2007 & later since there's virtually NO HELP on your machine and you have to go to the web (cloud-based help?) for any details; but for Office 2007, the basic instructions are the same as for all previous versions but the buttons are splattered randomaly among the 5 to 7 tabs and the 70 to 90 butttons under each tab - (with new names in most cases).

Step by step instructions:

Create and print mailing labels for an address list in Excel at Microsoft Office Support.

The examples show a US address format -

<<Name>>
<<Street_Address>>
<<City>>, <<State>> <<ZIP>>

but you can create your own layout by creating your own Merge-field with -

<<Name>>
<<Bldg>>
<<County>>
<<City>>
<<Location>>
<<Post_Code>>

(or whichever other format you need)

The ITEM NAMES must be specifically and separately identified in your database, and each data item that you need to locate separately on the labels must be in a NAMED column. If they're not properly defined there you may be limited in how you can edit the formatting. If the Excel database is not set up the way you want, you probably can apply fairly simple global-replace (possibly multi-step with dummy markers), column splits, column merges and other gimmicks to rearrange (in a copy) the existing data.

If the database has ever been used previously, perhaps the user with the Word 2003 has a template that's been successful before, and just doesn't remember where (s)he put it.

Alternatively, the Excel spreadsheet can be imported (opened and saved) into any later Office version where it would be better worth your time and effort to learn how to do it as efficiently as possible.

John

Subject: RE: Tech: Mailing Labels Word 2003
From: Arthur_itus
Date: 18 Mar 11 - 04:19 AM

RTFM agreed there John. Also many people only know a small percentage of Word and Excel capabilty and very often badly learned.

I do believe that SPB has a problem becuase the address includes the postcode in the same cell.

If so, the column of data needs to be split, which will then make it easy to use in the label.

If the address data is consistent, i.e London LN1 3DJ, then it becomes a 2 second job to get the data split into 2 seperate columns. Thus making it possible to do the mailing correctly.

Subject: RE: Tech: Mailing Labels Word 2003
From: SPB-Cooperator
Date: 18 Mar 11 - 05:11 AM

Each line of the address is in different cells
I know this as I spent half a day splitting the addresses in the imported data. Well to be precise typing in comma delimiters then using split cell.

The problem is in using the wizards, which in my opinion leave a lot to be desired in terms of flexibility.

my current problem is word changing the date format format UK to US.

Subject: RE: Tech: Mailing Labels Word 2003
From: JohnInKansas
Date: 18 Mar 11 - 06:13 AM

I'm not sure if you meant "date format" or "data format" above.(?)

Office programs are, to some degree, "regionalized" based on where they're purchased. You may be able to get a result closer to what you want by just changing the "language" set in Word Options/Preferences or whatever it was called in Word 2003. (Choosing UK English instead of US English may have an effect.)

Options that you set in Word generally control what all the other Office programs do.

Your "template" has to tell the merge process which data items go in what places on the label, and as long as the postal code is a separate item you should be able to put it where you want it on the label and Word should put it where you tell it to.

Most "experienced" wp operators probably don't use the Wizard that's built into Word. If you figure out how to type the proper information into the necessary fields, it's easier and quicker just to keyboard the whole thing; but the process is just barely complex enough that you likely do need to do a bit of study to be able to rip 'em off (labels & addy lists) fluently.

John