multi line cells

[Joanne]

I have a contact file in excel that has the data all in individual
cells and I need to get it into 3 lines of info per cell.

Here is the scenario

A1 John Doe
A2 110 Main Street
A3 Anytown, USA

I need to get into this format:
A1 John Doe
110 Main Street
Anytown, USA

A2 Jane Doe
110 Main Street
Anytown, USA

etc etc for approx 600 Contacts

I wanted to do a macro to get me started, but I don't know how to cut
and paste the information from the individual cells into one cell, so
I am stymied right from the get go. I am quite familiar with VBA in
MSWord 2003 and a bit with Outlook, but have no experience at all in
Excel.

Am using MSOffice Pro 2003 on WinXP Pro - the file consists of 3 line
addresses with one blank row in between each contact.

Any help or direction towards help will sure be appreciated.

Thanks
Joanne

 

[NickHK]

Joanne,
Personally, I would avoid combining this data into a single cell, because
that make checking, analysis and sorting much more difficult.
Why do you want all the info in a single cell ?

NickHK

 

[LenB]

If you only need to do this once, or not very often, it can be done with
a formula. For example, format column B to have wordwrap on, and put
this formula in B1, then copy/paste into every fourth cell in B, or fill
down by selecting B1 thru B4 and dragging down the fill box.

=A1 & CHAR(10) & A2 & CHAR(10) & A3

(Char(10) is linefeed, and formatting with wordwrap recognizes it)
Then you could paste the values from B onto another sheet and remove the
blanks. Then you still have your original data in a much better form,
as stated by NickHK.

Having said that, here's one way to do this with a macro, for
educational purposes only ;-)
It will overwrite the current sheet column b, and sheet2 column A.
Start it with the data sheet active.

Sub CombineAddresses()

Columns("B:B").WrapText = True
Range("B1").Activate
Do While Len(Trim(ActiveCell.Offset(0, -1).Value)) > 0
ActiveCell.Value = ActiveCell.Offset(0, -1).Value & vbLf & _
ActiveCell.Offset(1, -1).Value & vbLf & _
ActiveCell.Offset(2, -1).Value
ActiveCell.Offset(4, 0).Activate
Loop
Columns("B:B").Select
Selection.Copy
'change "Sheet2" below to where you want the output...
Worksheets("Sheet2").Activate
Range("A1").Activate
ActiveSheet.Paste
Range("A1").Select
Do While Len(Trim(ActiveCell.Value)) > 0
'delete the blank cells. There is probably a more
'elegant way to do this but ....
ActiveCell.Offset(1, 0).Delete shift:=xlUp
ActiveCell.Offset(1, 0).Delete shift:=xlUp
ActiveCell.Offset(1, 0).Delete shift:=xlUp
ActiveCell.Offset(1, 0).Activate
Loop

End Sub

HTH
Len

 

[Joanne]

Len
Thank you so much for your help. This is a mailing list that I am
working on that was created by putting the info in separate cells (I
know not why, being new on the block).

So a one time app is good and since I only need to get them into the
multi-line cell format (BYW, I googled and found out that the
alt+enter combo will let me put multi lines in manually) and that is
the only use for the spreadsheet, I was thinking that while either way
will work I would like to use the sub routine to move them (a little
practice in Excel VBA is something I need) into row B and then just
delete row A, using this much of your coding:

Sub CombineAddresses()

Columns("B:B").WrapText = True
Range("B1").Activate
Do While Len(Trim(ActiveCell.Offset(0, -1).Value)) > 0
ActiveCell.Value = ActiveCell.Offset(0, -1).Value & vbLf & _
ActiveCell.Offset(1, -1).Value & vbLf & _
ActiveCell.Offset(2, -1).Value
ActiveCell.Offset(4, 0).Activate
Loop
Columns("B:B").Select
Selection.Copy

End Sub

Am I correct that this is enough code to do the job?

Can you point me to some on-line resources for questions like mine -
Now that I am started, I think I'll noodle around with Excel and see
what else I can clean up quickly.

Again, thanks a million for your time and expertise - you have started
my day off on an up note.

Joanne

 

[Joanne]

Thanks for your time and thoughts on this Nick
What I am doing is trying to reformat a 'mailing list' that is layed
out in Excel in the manner I wrote about; so when trying to use it to
print labels, each cell grabs its own label. Not a good thingy! I
don't know why it was set up this way or even why it is in excel. I am
thinking that after I get it properly setup I may try my hand at
importing it into a table in access and doing the mailing thing from
the database app instead of the spreadsheet app.

Again, thanks for your interest. You guys are great.

 

[GregR]

Joanne, IMHO the best way to accomplish what you want, is to transpose
the address info in column A into one row for each address, in other
words each row becomes a seperate record. Then you can use this data
to mail merge in word and have your labels. Example

A B C
Name Address city, state zip

 

[Joanne]

GregR
thanks for your input.
your approach seems simple and to the point.
But I am not sure how to break it out in this direction.
Leave row 1 alone, grab row 2 col A and put it in row 1 col B, grab
row 3 col A and put it into row 1 col C. I'm sure there is a VBA
solution to this. Can you help me with it or point me to the necessary
info to do it myself?
Thanks again.
Joanne

 

[LenB]

Hi Joanne. Glad I could help you. Here I am learning something too - I
didn't know about alt+enter. You are correct about the block of code
for creating column B. You don't need those last two lines that select
column B and copy it. You could use the second loop I provided to
delete the blank cells in column B too. Remove the lines that select
sheet 2 and paste. Start with range("B1").activate instead of
range("A1").select then include the second Do loop. Hope that is clear.
Are you familiar with using F8 to step through the code to watch it
work? I shrink the VBA window and put it to the right so I can see the
Excel sheet and the program side by side.

The loop you quoted can be modified to put the data into rows as
suggested by GregR. Give it a try. Start in column D instead of B.
There will be two statements in the loop something like (untested):
activecell.offset(0,-2).value = activecell.offset(1,-3).value
activecell.offset(0,-1).value = activecell.offset(2,-3).value

As for places to ask questions, this is a great place. I have learned
90% or more of my Excel specific programming here just by reading other
peoples' questions and answers.
Some other sites I have used:
http://www.contextures.com/tiptech.html
http://www.j-walk.com/ss/excel/tips/index.htm
http://www.mvps.org/dmcritchie/excel/excel.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.cpearson.com/excel.htm

Len