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.

 

[Lilliabeth]

OK, give up, but don't you think it is time you learned to copy and paste?

1. Select this: =IF(ISBLANK(A2),"",B1&"~"&A2)
2. Hold down Ctrl and press c
3. Double-click on cell B2
4. Hold down Ctrl and press v
5. Press Enter
6. Press the up arrow key once
7. Hover over the fill handle and drag down at least as far as your addresses in Col A go.

Here is a picture of the fill handle:

http://www.ehow.com/how_2284668_use-fill-handle-microsoft-excel.html

--Lilliabeth

 

[SkipVought]

I type the formula into B2, I highlight it and click copy.

[red]
Wrong.[/red]

Type or paste the formula in to B2.

HIT ENTER!!!

Select B2 & Copy cell B2, NOT THE FORMULA IN B2.

Select B2 to B49

PASTE.




Skip,

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

 

[G0AOZ]

It's not copy and paste that's the problem here, it's my complete lack of knowledge concerning Excel! I've never trained in it or used it, so I don't know what to expect from it when certain actions are taken...

Once again, thank you for your instructions, and your patience. However, I am not convinced I'm making progress, but if you have time to look at these screen shots, you'll see what I've been trying to explain. (Hover over the image and click full size to see more detail).

This shows the formula typed into B2:-

http://s151.photobucket.com/albums/s136/G0AOZ/?action=view&current=Image1.jpg

This shows it AFTER hitting ENTER:-

http://s151.photobucket.com/albums/s136/G0AOZ/?action=view&current=Image2.jpg

This shows the highlighted area B2 - B49:-

http://s151.photobucket.com/albums/s136/G0AOZ/?action=view&current=Image3.jpg

And this is the result after pasting:-

http://s151.photobucket.com/albums/s136/G0AOZ/?action=view&current=Image4.jpg

Is that really what you'd expect to see, or is something else wrong?

Whilst I appreciate that this jumps to a separate part of the problem, do you have any ideas about the non-printing aspect of mail merge that I've encountered?

ROGER - G0AOZ.

 

[SkipVought]

That's EXACTLY what it should look like.

Next Step:

Select that entire column

COPY

Right-Click in the selection and select PasteSpecial. In the PasteSpecial Window, Paste Section, select the Values option. This changes the formulas to values in that column.

With that range STILL SELECTED, Data > Text to columns..

In the Convert Text to Columns Wizard there are 3 steps...

1. Select DELIMITED option. NEXT

2. In the Delimiters boxes, enter ~ in the OTHER textbox (the Other checkbox automatically becomes checked)

3. You have nothing to do in this step, just FINISH.

Now you have a column for each of the lines of your address. But there's one other thing to do. Paste this formula into A2...
[tt]
=if(b3="",1,0)
[/tt]
Copy and paste down thru your rows of data. This should return a 1 for each row that has ALL your address data in the same row.

Add Column Headings on row 1.

Save

Now you have an address file ready to use, if you filter the source data, in Word, on the first column on 1.


Skip,

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

 

[G0AOZ]

Great! I had no idea it was going to look like that...

Went through your next steps, but feel I must've gone wrong, although can't see where. Probably just my lack confidence with this but when I opened up Word and started the mail merge, here's what I see in Mail Merge Recipients box (the saved Excel file):-

http://s151.photobucket.com/albums/s136/G0AOZ/?action=view&current=Image5.jpg

I can see where the filter is but not sure how to get it to produce what I want.

ROGER - G0AOZ.

 

[SkipVought]

1. There is a problem in Excel. A18 (between MK7 2XJ and GREENFIELDS CONSTRUCTION LTD.) should be BLANK and is not. Redo the entire process with the formulas.

2. You have nothing to filter on in order to select ONLY the rows containing ALL the columns you need. You need to insert a column in the left (column A) in Excel with this formula...
[tt]
A2: =IF(COUNTA(B2:IV2)>COUNTA(B3:IV3),1,0)
[/tt]
Paste down thru your rows of data. The rows with 1, have all your rows of data. You can use that column to FILTER in MS Word MailMerge.

Skip,

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

 

[G0AOZ]

No progress, gone backwards if anything...

SkipVoight and Lilliabeth, thank you both for your extreme patience. My frustration with this has reached a point where I must regrettably pack it in for the sake of my sanity. I cannot face any more sleepless nights over this. Excel has now been erased from this hard drive, and I shall try and avoid ever touching it again if at all possible...

ROGER - G0AOZ.

 

[SkipVought]

Roger,

I am extremely disappointed with your lack of resolve.

Put it down for a while. Walk away for a time.

But for goodness sake, do not give up! If you were within my call plan, I'd suggest a dial up, to help walk you thru. But the pond is wide.

Skip,

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

 

[G0AOZ]

Skip, it would be really great to solve this issue, and I assure you very sincerely that is what I'd dearly love to achieve. However, I have spent a very considerable amount of hours on this for no result, I simply have to go and earn a living and introduce myself to my wife, who has forgotten what I look like! Believe me, I'm mad as hell with myself for not being able to get to grips with it!

I generally try not to get involved with issues surrounding Word, Excel and the like, as I've no experience or training in them, so I pass the user on to an experienced Microsoft Office tech. I usually stick to what I do best which is fix busted or virused computers, etc., but when no one is available I will try and take ownership of a software problem like this one.

You're right, I need to put it down for a while, and come back to it later. I appreciate your encouragement...

ROGER - G0AOZ.