Printing envelopes from a list of addresses in Word.

[G0AOZ]

One of my users has typed in his client names and addresses into a basic Word (2002) document. There are probably hundreds! He now wants to print each name and address directly onto an envelope. There is no letter to be merged with these addresses, he merely wants the addresses printed onto his envelopes.

Is there an easy (and quick!) way to do this? He has available Word and Excel 2002. I am a hardware and systems man, but am only a basic user of Word and Excel, but am trying to help my client...

Any help appreciated.

ROGER - G0AOZ.

 

[GSCaupling]

Roger,

My first thought would be to convert the text in MS Word to a table. Hopefully it is in a uniform format such as:

Johnny b. Good
123 Same St.
Washington, DC 12345

Errol Flynn
308 North Ave.
Hollywood, CA 90646

Jenn Anniston
8087 Big Street
Bevvly Hills, CA 90644

(NOTE: convert this to FOUR columns to account for the blank line between entries).

Step two: copy the table to Excel, add column headings, and then do a merge to print the envelopes.

Good Luck!
GS


[small][navy]*****************^*****************[/navy][/small]
[red]I think of the word processor as the pen's e-quill.[/red]

 

[G0AOZ]

Don't think that will work as the user typed it out like this in two columns:-


Johnny b. Good Errol Flynn
123 Same St. 308 North Ave.
Washington, DC 12345 Hollywood, CA 90646

Freddy Mercury Jim Smith
456 Main St. 555 South Ave.
Washington, DC 12345 Hollywood, CA 90646

etc etc etc etc


And they've just tabbed across between the addresses on the same line. You can move things into some semblance of order but it has to be done line by line. Not worth it in my view - they might just as well re-type the whole thing properly into an address database, which Word has got inbuilt.

Thanks for your idea though...

ROGER - G0AOZ.

 

[SkipVought]

Hi,

This is exactly what the MailMerge feature is designed to do.

However the source data for this feature must be a properly structured TABLE, either in a Word document saved with only that data, or an Excel workbook, having that table in one of its sheets.

A properly structured table has columns for...
[tt]
NAME - could be the full name or separate columns for First & Last
ADDRESS
CITY, ST ZIP - could be conbined or separate columns
[/tt]
However, the example you just posted is not useable for this feature.





Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[G0AOZ]

SkipVought, you've confirmed my thinking on this one! Thanks.

ROGER - G0AOZ.

 

[Lilliabeth]

Copy the Word data
Pase it into Excel
Copy the Excel data
Paste Special > Transpose
Then cut the rightmost columns and paste them below the other columns

--Lilliabeth

 

[PepperP]

I just solved this problem, getting the table layout to Excel so that you can do any kind of Merge. See:

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

for a solution

 

[G0AOZ]

Lilliabeth - after changing a lot of the (weird?) settings they had within the original Word document, I can eventually end up with the details in a single column in Excel. However, still cannot do what I want with it! Remember, I am no whizz with Excel!

In Excel I can get everything into column A but each line of all the addresses is in a different cell, like this:-

A

1 Johnny b. Good
2 123 Same St.
3 Washington, DC 12345
4
5 Freddy Mercury
6 456 Main St.
7 Washington, DC 12345
8
9 Errol Flynn
10 308 North Ave.
11 Hollywood, CA 90646


Not sure how I can easily merge all these cells together.


PepperP - Unfortunately, they never typed it out in a LABEL format! For starters they had a hanging paragraph so the lefthand column of names and addresses does NOT have "soft returns". Taking out the hanging paragraph results in the addresses virtually merging together, save for a "tab"? between them.

Thinks... If the code for a "soft return" is ^13 then maybe if I knew the code for a "tab" I could use that instead?

ROGER - G0AOZ.

 

[Lilliabeth]

My understanding is that a hanging paragraph is a paragraph where all but the first line is indented on the left side.

You can use the button marked Special in Word's Find and Replace dialog box to see thatthe code for tab is ^t.

If they are always 3 lines long, then you have a very easy fix... are the addresses always 3 lines long or are they sometimes 4?





Drag



--Lilliabeth

 

[Lilliabeth]

If the addresses are always 3 lines, use the arrangement in Excel that you described in your post above where all the data is in one column. This formula presumes that your data starts in cell A2.
Put this in B2
=IF(ISBLANK(A1),CONCATENATE(A2,"~",A3,"~",A4),"")

Then copy it down.

Then with the formulas selected, hit Copy

Paste values (in version 2003, it is Edit > Paste special > Values. In 2007, Values is an option on the Clipboard drop-down.)

Now with the former formulas selected, do a Text to Columns (on the Data menu in 2003, on the Data tab in 2007.

File type is Delimited. The delimited is Other ~.

Select the range and sort it to remove empty rows.

Done.

--Lilliabeth

 

[Lilliabeth]

I meant the Delimiter is Other ~

--Lilliabeth

 

[G0AOZ]

Thank you for your continuing help Lilliabeth.

Addresses vary from 3 lines to 6 lines. Cannot get the formula to appear "as is" in B2 - it shows as #NAME? and I'm guessing this is because I have varying numbers of lines in the addresses.

ROGER - G0AOZ.

 

[Lilliabeth]

No, the formula should work. Did you paste it into a cell and then look at the Formula bar to see that it still looked right?
Regardless, since the addresses are of varying lines, and you have a blank cell seperating one address from another, you can use something along these lines in cell B2.
=IF(ISBLANK(A2),"",B1&"~"&A2)

Again, this assumes your data begins in cell A2.

Then you can use a formula that will return the contents of cells that have a blank cell below then use Text to Columns and you're done.

--Lilliabeth

 

[G0AOZ]

Not having any success with this, as I'm obviously doing something wrong...

I have now changed it so the first block of data falls in A2 like this, and shown the different address lengths I'll be coping with:-

A B

1
2 Johnny b. Good
3 123 Same St.
4 Washington
5 DC 12345
6 USA
7
8 Freddy Mercury
9 456 Main St.
10 Washington
11 DC 34567, USA
12
13 Errol Flynn
14 308 North Ave.
15 54th Street
16 Hollywood
17 CA 90646
18 USA


I now type this =IF(ISBLANK(A1),CONCATENATE(A2,"~",A3,"~",A4),"") in B2. What do I do next? If I hit <Enter> after typing it, the content of B2 reads like this:-

Jonny b. Good~123 Same St.~Washington

and nothing else.

I also do not understand how to "Then copy it down". Do you mean this formula has to go into every cell of column B for which there is a corresponding entry in column A including blanks like lines 7 and 12? If so, how?


ROGER - G0AOZ.

 

[Lilliabeth]

Since your addresses vary in length, you must use this formula instead.

=IF(ISBLANK(A2),"",B1&"~"&A2)

Do you mean this formula has to go into every cell of column B for which there is a corresponding entry in column A including blanks like lines 7 and 12? If so, how?

Yes. Actually the formula will go into all cells in column B, from B2 all the way down to however far your addresses go.

Generally you would use the Fill handle for this: Select the cell with the formula in it (B2). Hover over the Fill handle to get a black plus sign then drag all the way down however far the data in Col A goes.

The Fill handle is the tiny square in the lower right corner of a selected cell or range.



--Lilliabeth

 

[G0AOZ]

Lilliabeth, thank you for your help but no way can I get this to work...

I have now gone back to PepperP's solution at:-

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

Managed to convert the text to a table (in Word), and all looks good. But, Word's Mail Merge now won't play!

In Word 2002 I want to print envelopes.
Select Tools > Letters and Mailings > Mail Merge Wizard.

Click on Envelopes > Starting Document > Envelope Options select envelope size and click Ok.

Click Select Recipients, click Browse, select previously made Word document (table). This now displays as a box on screen as Mail Merge Recipients and contains all the names and addresses. Click Select All then click Ok.

Click Arrange your envelope, click Address block. The Insert Address Block box appears on screen, and preview looks good, so click Ok.

Now ««Address Block»» appears on screen in top left hand corner, so I use Page Setup and Margins to place it more centrally on the envelope.

Now I click Preview your envelopes and and the (envelope) screen is blank, save for the cursor. Needless to say when I Complete the merge and Print all the envelopes are blank!

Can someone point out what I'm doing wrong here please...

ROGER - G0AOZ.

 

[Lilliabeth]

Lilliabeth, thank you for your help but no way can I get this to work...

I put a bit of work into solving this for you and think it would be nice if you could tell me why there is no way you can get this to work. What is the problem please?


--Lilliabeth

 

[Lilliabeth]

Really, this is a very easy thing since I am providing the formulas.
After doing this one starting in B2: =IF(ISBLANK(A2),"",B1&"~"&A2)
Then Copy then Paste values.

Then in another column you need this formula: starting in Row 2 of another column:
=IF(LEN(B3)=0,B2,"")

Then Copy, Paste Values.
Then Data > Text to Columns using ~ as the delimiter. Done!
Sort the list to remove the blank rows. You will add labels to the top of each column like this: Line1, Line2, Line3, etc, which will work as the data source in a mail merge.

But since you think mail merge should be working with the data you have,you apparently have success at creating a data table. How many fields do you have and what are the fields named?

--Lilliabeth

 

[SkipVought]

"I also do not understand how to "Then copy it down"."

Assuming that your list goes from A2 to A999.

Paste Lilliabeth's formula =IF(ISBLANK(A2),"",B1&"~"&A2) in B2.

Copy B2

Select B2:B999

PASTE.

This is Excel 101, my friend. It WORKS!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[G0AOZ]

As I said, I really appreciate your efforts, and I would very much liked to have resolved this issue. However, my main problem is I'm running out of time trying to solve this one before my user gives up and types in the whole jolly lot again! That's why I thought I'd try PepperP's hint. However, got so far with that, and then got stuck.

Why won't it work? I type the formula into B2, I highlight it and click copy. Selecting B2 down to B49 and then clicking paste does nothing other than put B3:B49 ahead of the formula in the B2 cell. (I'm currently just playing with 8 addresses at the moment). Also tried highlighting from above the B2 cell to see if that would help, but got the following message:

Microsoft Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference... Etc., etc.

Skip, I'm sure it does work, but not here for me! I'm not blaming anyone other than myself for this - I know you guys have done your best for me...

ROGER - G0AOZ.