merging cells and mailing lists

  • Thread starter Thread starter B Gary
  • Start date Start date
B

B Gary

HOW DO I MERGE CELLS IN ONE COLUMN AS SO:

BRANDI
111 11 STREET NORTH
ANYWHERE, AL 12345

THEY ARE CURRENTLY IN 3 DIFFERENT CELLS. I AM TRYING TO
CREATE A MAILING LIST. ALTHOUGH IF YOU HAVE AN EASIER
APPROACH I WIILL SURELY APPRECIATE. BECAUSE I HAVE TO GO
THROUGH 1449 CELLS.

HELP ME PLEASE
 
Are you saying that each name/address/city,st,zip is a group of 3 cells. And
there are no blank rows in between?

Assuming your data starts in A1, then put this in B1:
=INDEX(A:A,(ROW()-1)*3+1,1)

Put this in C1:
=INDEX(A:A,(ROW()-1)*3+2,1)

And D1:
=INDEX(A:A,(ROW()-1)*3+3,1)

And drag them down until you're out of data.
(and copy|paste special values)

And if you decide to split up the city, state, zip.

Select column D and do data|text to columns.
tell excel it's delimited by a comma.

Then you'll have the state and zip in a column.
another data|text to columns
(delimited by space)
and you'll have the state and zip in different columns.

(depending on how clean your data is, you might have to go back and tweak some
badly formatted addresses.)
 
I'M NOT DEAF. Sorry, please do not use all caps, it's the equivalent of
shouting.

That aside, a couple of approaches:

First and before you do anything else, back up your data!

1. Assume all your data is in cloumn A and the Cell containing BRANDI is
in A2, then in B2, enter the formula "=a2 & " " & a3 & " " & a4"
this would put all your addresses on one line, not necessarily what you need
2. in cell b2 enter the formula "=a2"
in cell c2 enter the formula "=a3"
in d2 "=a4"
this is a better approach as you can then use the data in standard mailing
labels, form letters etc

copy your new formulas down to row 5, then copy and paste an ever increasing
range until you reach the bottom of your data.

Now copy the cells containing the formula(e), and PASTE SPECIAL (yes I am
emphasising) PASTE SPECIAL and select values. This will copy the cell
contents and paste it back in place as a value rather than a formula.
You can then sort the columns to eliminate the blank rows, and you now have
a usable mailing list.

Make sure you add column headers (name, address, town) to the columns and
save the excel file. You could delete the original data from column A, or
not as you prefer.

Now go into Word, and select Tools/Letters and Mailings (in XP) or whatever
the variant is on your version of word.
When prompted to select your data source, do the browse thing, and make sure
you select Excel Files as the file type. Browse to the workbook containing
the data and if prompted select entire workbook.

you can then use the word mail merge toolbar to insert the fields containing
the data in the correct locations in your mailing list, form letters, label
documents etc.

Steve
 
Back
Top