Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
multi line cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
multi line cells
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 "Joanne" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
multi line cells
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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
multi line cells
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 LenB wrote: 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 wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
multi line cells
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. NickHK wrote: 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 "Joanne" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
multi line cells
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 Greg Joanne wrote: 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 LenB wrote: 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 wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
multi line cells
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 GregR wrote: 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 Greg Joanne wrote: 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 LenB wrote: 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 wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
multi line cells
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 Joanne wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate: Multi-Line Cell with 2 single line cells | Excel Worksheet Functions | |||
macro code for grouping adjacent cells into one multi-line cel | Excel Programming | |||
macro code for grouping adjacent cells into one multi-line cel | Excel Programming | |||
copy/paste excel to texteditor -> multi-line text cells gain unwanted double-quotes | Excel Discussion (Misc queries) | |||
how can I paste multi-line/multi-paragraph data into ONE cell? | Excel Discussion (Misc queries) |