# Excel/Word/Mail Merge question **URL:** \ **Category:** Factual Questions\ **Created:** [December 22, 2005, 9:43pm UTC](https://boards.straightdope.com/t/excel-word-mail-merge-question/336867 "2005-12-22T21:43:10Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![Gfactor](https://avatars.discourse-cdn.com/v4/letter/g/9de053/32.png) [@Gfactor](https://boards.straightdope.com/u/Gfactor)\ **Post date:** [December 22, 2005, 9:43pm UTC](https://boards.straightdope.com/t/excel-word-mail-merge-question/336867/1 "2005-12-22T21:43:10Z")
I have an Excel (I’m running Excel 2002, if that matters) spreadsheet with names and addresses and I need to print envelopes from them. Here’s the catch. Each address takes up two rows. It looks like this: 1 Street Address 2 City, State Zip Like this: A B 1 Street Address 2 City, State Zip Name The recipient’s name is in a separate column, in the same row as City, State Zip. I had planned on using Word to create a mail merge document and then just run the merge. Of course, Word thinks that each row is a separate record. So I need to either: 1. Merge pairs of rows preferably as a batch; or 2. Figure out a way to get Word to treat two records as one recipient. It seems like this should be easy, but it’s not happening for me. Can anyone help? ---
**Author:** ![Duckster](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/duckster/32/1244_2.png) [@Duckster](https://boards.straightdope.com/u/Duckster)\ **Post date:** [December 22, 2005, 9:57pm UTC](https://boards.straightdope.com/t/excel-word-mail-merge-question/336867/2 "2005-12-22T21:57:09Z")
Did you set up your spreadsheet with each column holding a unique piece of data such as this: FirstName | LastName | Street | City | State | Zip Each row of data makes up one record. With Word, your form should look like this: \ \ \ \, \ \ Lost? The Help function in Word is pretty good. ---
**Author:** ![missbunny](https://avatars.discourse-cdn.com/v4/letter/m/76d3ee/32.png) [@missbunny](https://boards.straightdope.com/u/missbunny)\ **Post date:** [December 22, 2005, 10:37pm UTC](https://boards.straightdope.com/t/excel-word-mail-merge-question/336867/3 "2005-12-22T22:37:44Z")
How many names do you have? You should edit the spreadsheet so that the city/state/ZIP is in another column. ---
**Author:** ![missbunny](https://avatars.discourse-cdn.com/v4/letter/m/76d3ee/32.png) [@missbunny](https://boards.straightdope.com/u/missbunny)\ **Post date:** [December 22, 2005, 10:40pm UTC](https://boards.straightdope.com/t/excel-word-mail-merge-question/336867/4 "2005-12-22T22:40:27Z")
I mean by using a formula, not by retyping anything. ---
**Author:** ![Jpeg\_Jones](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@Jpeg\_Jones](https://boards.straightdope.com/u/Jpeg_Jones)\ **Post date:** [December 22, 2005, 10:58pm UTC](https://boards.straightdope.com/t/excel-word-mail-merge-question/336867/5 "2005-12-22T22:58:12Z")
There is no way to tell Word to treat 2 Excel rows as one record. You have to reformat the spreadsheet so that each row is an individual record. It shouldn’t be too hard, though. Use lots of “=” formulas. ---
**Author:** ![Gfactor](https://avatars.discourse-cdn.com/v4/letter/g/9de053/32.png) [@Gfactor](https://boards.straightdope.com/u/Gfactor)\ **Post date:** [December 23, 2005, 12:00am UTC](https://boards.straightdope.com/t/excel-word-mail-merge-question/336867/6 "2005-12-23T00:00:18Z")
> [@Duckster](#): > > Did you set up your spreadsheet I didn’t create it. I inherited it. Had I set it up, I would have done it the way you indicate. > [@missbunny](#): > > How many names do you have? 600 names. ---
**Author:** ![UncleRojelio](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/unclerojelio/32/3160_2.png) [@UncleRojelio](https://boards.straightdope.com/u/UncleRojelio)\ **Post date:** [December 23, 2005, 12:04am UTC](https://boards.straightdope.com/t/excel-word-mail-merge-question/336867/7 "2005-12-23T00:04:01Z")
> [@Gfactor](#): > > 600 names. PERL is your friend. ---
**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\ **Post date:** [December 23, 2005, 12:06am UTC](https://boards.straightdope.com/t/excel-word-mail-merge-question/336867/8 "2005-12-23T00:06:34Z")
I do this kind of crap for a living. If you want to send it to me, I can fix it for you. It will take about 5 minutes. E-mail is in my profile. ---
**Author:** ![Gfactor](https://avatars.discourse-cdn.com/v4/letter/g/9de053/32.png) [@Gfactor](https://boards.straightdope.com/u/Gfactor)\ **Post date:** [December 23, 2005, 12:17am UTC](https://boards.straightdope.com/t/excel-word-mail-merge-question/336867/9 "2005-12-23T00:17:53Z")
> [@Shagnasty](#): > > I do this kind of crap for a living. If you want to send it to me, I can fix it for you. It will take about 5 minutes. E-mail is in my profile. Hey, thanks. ---
**Author:** ![Gfactor](https://avatars.discourse-cdn.com/v4/letter/g/9de053/32.png) [@Gfactor](https://boards.straightdope.com/u/Gfactor)\ **Post date:** [December 23, 2005, 12:43am UTC](https://boards.straightdope.com/t/excel-word-mail-merge-question/336867/10 "2005-12-23T00:43:35Z")
He wasn’t kidding. It is done. 😃 Mods, you may lock this thread. Thanks **Shagnasty**. ---
**Author:** ![missbunny](https://avatars.discourse-cdn.com/v4/letter/m/76d3ee/32.png) [@missbunny](https://boards.straightdope.com/u/missbunny)\ **Post date:** [December 23, 2005, 2:17am UTC](https://boards.straightdope.com/t/excel-word-mail-merge-question/336867/11 "2005-12-23T02:17:24Z")
**Shagnasty** , out of curiousity, how did you do it? I would probably have done =concatenate and then do a text-to-columns. Am wondering if there’s a quicker way for that many names. (I have quicker ways for just a few names for 600 it would be too much work.) ---
**Author:** ![Anaamika](https://avatars.discourse-cdn.com/v4/letter/a/5f9b8f/32.png) [@Anaamika](https://boards.straightdope.com/u/Anaamika)\ **Post date:** [December 23, 2005, 2:21am UTC](https://boards.straightdope.com/t/excel-word-mail-merge-question/336867/12 "2005-12-23T02:21:11Z")
yes, Shag, can you share, please? ---
**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\ **Post date:** [December 23, 2005, 2:26am UTC](https://boards.straightdope.com/t/excel-word-mail-merge-question/336867/13 "2005-12-23T02:26:41Z")
> [@missbunny](#): > > **Shagnasty** , out of curiousity, how did you do it? I would probably have done =concatenate and then do a text-to-columns. Am wondering if there’s a quicker way for that many names. (I have quicker ways for just a few names for 600 it would be too much work.) The parts of the address were on two different rows. I simply went to the cells next to the part that was already entered and pulled in the parts in the right order. I am making this up from memory. The address is in column A, B, and C but A is down one row from B, and C. I just went to the cells next to it and entered: (d2) =A3 (e2) = b2 (f2) = c2 I filled that down all the way. That will create half the cells that are correct and the other half that are just crap parts from the way the data is set up. Highlight all the new cells with formulas, select Copy, and then Paste Special - Values. This turns the cells into text rather than the results of formulas. This is necessary for the next steps. I was curious what it would take to seperate the bad half from the good half. A simple sort on a junk data cell took care of that easy as pie. The good addresses came to the top. Manually delete the garbage cells and you’re done. Total Time: 4 minutes. ---
**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\ **Post date:** [December 23, 2005, 2:34am UTC](https://boards.straightdope.com/t/excel-word-mail-merge-question/336867/14 "2005-12-23T02:34:45Z")
I just looked at the OP again and my cell references that I just listed are different than they really were. The technique is exactly the same tough. 1. Just get the data into other cells in the right order. 2. Fill all the formulas down. 3. Turn it to text instead of formulas. 4. Figure out a way to identify the good and the bad. Some type of sort usually works. 5. Delete the bad stuff. I have to do this type of thing all the time. It is quite useful. ---
**Author:** ![missbunny](https://avatars.discourse-cdn.com/v4/letter/m/76d3ee/32.png) [@missbunny](https://boards.straightdope.com/u/missbunny)\ **Post date:** [December 23, 2005, 3:02am UTC](https://boards.straightdope.com/t/excel-word-mail-merge-question/336867/15 "2005-12-23T03:02:12Z")
That’s the other way I thought of doing it. Isn’t Excel fun? I love it. And I’m not even an accountant! ---
**Author:** ![Duckster](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/duckster/32/1244_2.png) [@Duckster](https://boards.straightdope.com/u/Duckster)\ **Post date:** [December 23, 2005, 6:05am UTC](https://boards.straightdope.com/t/excel-word-mail-merge-question/336867/16 "2005-12-23T06:05:35Z")
Obviously, **Shagnasty** excels at this sort of thing. 😃