Mail Merge 2007 help..

[shawn]

Need help doing a mail merge. Normally when I have to type in addresses I do
them in Excel in this format:

Business Attn Address1 Address2 City State Zip

Those are all seperate columns. Then I mail merge them into Word onto Avery
5160 labels and print.

So for example:

Business Attn Address1 Address2 City State Zip
Catmando Attn: Mark Mando 1 Smith Street Suite 120 Beverly Hills CA 90210

The top part is set as header information and the mail merge works great.

We have to mail out over 900 catalogs tommorow! Someone made a list of 702
addresses before I even knew about this mailing. They did them in Word
format, but my problem is they did them like this:

Business
Attn
Address1
Address2
City State Zipcode

There is a space between every record. So for example the above address is
typed as:

Catmando
Attn: Mark Mando
1 Smith Street
Suite 120
Beverly Hills, CA 90210

So, what I was going to do is add this to the beginning of my file with 702
addresses:

Business#
Attn#
Address1#
Address2#
Beverly Hills, CA 90210#
$

The #'s will be field delimiters, the $ will be a record delimiter.

I have verified and tried with using 4 addresses as a test. It works great.
My problem is this: How do I add # to the end of every line easily? I don't
want to sit there and do it manually. Then I'll go in and manually change
some of those to $ to make them record delimiters.

I have Word 2007. Could someone tell me how to get # at the end of each line
or if there is an easier way to do this please let me know. Any help is most
appreciated.

 

[Doug Robbins - Word MVP]

If you turn on the display of non-printing characters and you see

Catmando¶
Attn: Mark Mando¶
1 Smith Street¶
Suite 120¶
Beverly Hills, CA 90210¶
¶
Next address

That is, there is a end of paragraph mark at the end of each line of the
address and there are two of the separating each address, use Edit>Replace
to replace ^p^p with #### and then use it again to replace ^p with ^t and
then a third time to replace #### with ^p

You should then have each address in a single paragraph with a tab space
between each component of the address. The you can use the Convert Text to
Table facility (Click on the Table pulldown on the Insert Ribbon) and you
should then get a table into which you can insert a row at the top into the
cells of which you enter the names of the merge fields. You can then save
that table and use it as the data source.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Herb Tyson [MVP]]

There might've been an easier way, such as using the Word file as a data
file, then creating a merge file that formats the addresses for output
however you like.

However, to answer the # question, assuming that each line ends with a
paragraph mark, you can use Find/Replace:

Find:^p
Replace with:#^p


If you then end up with $# in the delimiter lines, you can replace $# with
$.

 

[Graham Mayor]

It might be simpler to convert it to a table. Select the column and tell
Word to convert it to a table of 6 columns (instead of the default 1) then
remove the last column - see
http://www.gmayor.com/convert_labels_into_mail_merge.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[shawn]

Thanks for the help.

What had happened is I did do what was suggested.. I went into MORE, then
SPECIAL and I was using "Paragraph Character" -- this was ^v. I didn't even
see there was Paragraph Mark up top, which would have made it ^p.

 

[shawn]

That page Graham directed me to looks very easy to do. One problem is they
have two Paragraph Marks between records. I only have one.

How would I do a search for Paragraph Mark ^p but only if it has no text in
front of it? So in this example:

Catmando¶
Attn: Mark Mando¶
1 Smith Street¶
Suite 120¶
Beverly Hills, CA 90210¶
¶

Catmando¶
Attn: Mark Mando¶
1 Smith Street¶
Suite 120¶
Beverly Hills, CA 90210¶
¶

I would want to only replace those ^p's on a line by itself.. this way I
could follow that article more easy.

 

[Herb Tyson [MVP]]

Search for two consecutive ^p's.

For example, I usually do this with a series of find/replace steps:

Find:^p^p
Replace with:[record delimiter]

Find:^p
Replace with:[field delimiter]

If you want the record delimiter to be a ^p, you then follow up with:

Find:[record delimiter]
Replace with:^p

The important thing is the order. If you temporarily change all of the
double paragraph marks to something else, it's then possible to distinguish
between a single ^p used to separate data fields and ^p^p used to separate
data records.

 

[Graham Mayor]

It shouldn't matter as long as all the records have the same number of
lines. Just set the number of columns to match the number of lines you have
from one record to the next - presumably 5.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[shawn]

Alright. I got everything 99% done with all your help.

Just one thing I don't know.

I have everything in a table now.. each record is on it's own line. The top
has header information.

Only problem is when I Converted Text to Table it replaced the ¶ marks with
some mark that looks like a little circle with four lines on the outside.

I have a # sign at the end of each one of my lines.. I need to replace the #
sign and that circle with just the circle.. all my labels have a # sign at
the end of each line I am trying to get rid of, but I don't know what you
call that circle or what ^ I use to replace.

 

[shawn]

Here's a screenshot of what I am describing:

http://i7.tinypic.com/2vlnmhe.jpg

As you can see my lines end with # and that weird character. I want to get
rid of the # at the end of every line.. so I have to specify # and that
character in my replace.. I can't just say get rid of all # because some of
the addresses contain # in them.

 

[Peter Jamieson]

The weird thing is an "end of cell" marker.

If I were you, I would go through the process you already used again and
replace

#^p


by

^p

tto get rid of those hashes /before/ you do the text-to-table conversion.

Peter Jamieson

 

[Doug Robbins - Word MVP]

That is just the end of cell marker, if you Hide non-printing characters,
it will not be visible.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Graham Mayor]

And the hash marks # can be removed by searching for # replace with nothing.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>