Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging multiple cells with text
Hi,
I have many files that contains cells in which sentences were split in multiple contiguous cells. As an example, I have "How are" in A1, "you doing" in A2 and "today?" in A3. I need to merge these 3 cells by keeping the content of A1, A2 and A3 into A1. There could be a variable quantity of cells I need to merge in this manner. This means, sometimes it can be 3, sometimes 4, and so on. I know some VBA code could help me do this and save me valuable time but I am unsure where to start with this. Can anyone help me? Thanks in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging multiple cells with text
How would you know (or rather, VBA know) whether it is three or 4
cells to join together? Do you have a blank cell in between each group? Pete On Aug 28, 12:46 am, Gaetan wrote: Hi, I have many files that contains cells in which sentences were split in multiple contiguous cells. As an example, I have "How are" in A1, "you doing" in A2 and "today?" in A3. I need to merge these 3 cells by keeping the content of A1, A2 and A3 into A1. There could be a variable quantity of cells I need to merge in this manner. This means, sometimes it can be 3, sometimes 4, and so on. I know some VBA code could help me do this and save me valuable time but I am unsure where to start with this. Can anyone help me? Thanks in advance! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging multiple cells with text
Try a formula like this:
=TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E2) The TRIM formula will remove any leading & trailing spaces and the &" "& places a literal space between each of the cell entries. If you don't need any spaced between cell entries use the following: =TRIM(A1&B1&C1&D1&E2) -- Kevin Backmann "Gaetan" wrote: Hi, I have many files that contains cells in which sentences were split in multiple contiguous cells. As an example, I have "How are" in A1, "you doing" in A2 and "today?" in A3. I need to merge these 3 cells by keeping the content of A1, A2 and A3 into A1. There could be a variable quantity of cells I need to merge in this manner. This means, sometimes it can be 3, sometimes 4, and so on. I know some VBA code could help me do this and save me valuable time but I am unsure where to start with this. Can anyone help me? Thanks in advance! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging multiple cells with text
Pete,
I believe VBA can "know" how many cells by looking at how many cells are selected in the range. In the case I am looking for, cells would be in the same column. I would then proceed by selecting the range of cells and then applying the VBA code that would act like an improved merge cells function. "Pete_UK" wrote: How would you know (or rather, VBA know) whether it is three or 4 cells to join together? Do you have a blank cell in between each group? Pete On Aug 28, 12:46 am, Gaetan wrote: Hi, I have many files that contains cells in which sentences were split in multiple contiguous cells. As an example, I have "How are" in A1, "you doing" in A2 and "today?" in A3. I need to merge these 3 cells by keeping the content of A1, A2 and A3 into A1. There could be a variable quantity of cells I need to merge in this manner. This means, sometimes it can be 3, sometimes 4, and so on. I know some VBA code could help me do this and save me valuable time but I am unsure where to start with this. Can anyone help me? Thanks in advance! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging multiple cells with text
Kevin,
That would take me more time to enter this formula than to actually retype the text in the merged cells. Also, the amount of cells varies from time to time... it could be 3 at one point and 4 then other, or 5... thus preventing me from using the same formula in all cells. But thanks anyway... "Kevin B" wrote: Try a formula like this: =TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E2) The TRIM formula will remove any leading & trailing spaces and the &" "& places a literal space between each of the cell entries. If you don't need any spaced between cell entries use the following: =TRIM(A1&B1&C1&D1&E2) -- Kevin Backmann "Gaetan" wrote: Hi, I have many files that contains cells in which sentences were split in multiple contiguous cells. As an example, I have "How are" in A1, "you doing" in A2 and "today?" in A3. I need to merge these 3 cells by keeping the content of A1, A2 and A3 into A1. There could be a variable quantity of cells I need to merge in this manner. This means, sometimes it can be 3, sometimes 4, and so on. I know some VBA code could help me do this and save me valuable time but I am unsure where to start with this. Can anyone help me? Thanks in advance! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging multiple cells with text
Try something similar to:
For j = 0 To Application.WorksheetFunction.CountA(Range("A:A")) - 1 c = 0 Do While Range("A1").Offset(j, c).Value < "" MyJoin = MyJoin & Range("A1").Offset(j, c).Value c = c + 1 Loop Range("A1").Offset(j, 0).Value = MyJoin MyJoin = "" Next j -- Damon Longworth 2007 Excel / Access User Conference London, England - Currently rescheduled St. Louis, Missouri - Oct 24-26, 2007 www.ExcelUserConference.com/ "Gaetan" wrote in message ... Hi, I have many files that contains cells in which sentences were split in multiple contiguous cells. As an example, I have "How are" in A1, "you doing" in A2 and "today?" in A3. I need to merge these 3 cells by keeping the content of A1, A2 and A3 into A1. There could be a variable quantity of cells I need to merge in this manner. This means, sometimes it can be 3, sometimes 4, and so on. I know some VBA code could help me do this and save me valuable time but I am unsure where to start with this. Can anyone help me? Thanks in advance! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging multiple cells with text
Hi,
Concetenate funtions joins several text strings into one text string. For more information, please refer to the the topic on "Concetenate" in the Microsoft Excel 2003 online help. Challa Prabhu Does help "Gaetan" wrote: Hi, I have many files that contains cells in which sentences were split in multiple contiguous cells. As an example, I have "How are" in A1, "you doing" in A2 and "today?" in A3. I need to merge these 3 cells by keeping the content of A1, A2 and A3 into A1. There could be a variable quantity of cells I need to merge in this manner. This means, sometimes it can be 3, sometimes 4, and so on. I know some VBA code could help me do this and save me valuable time but I am unsure where to start with this. Can anyone help me? Thanks in advance! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging multiple cells with text
Alright,
I have used part of your code to help me out contruct this code. With this one, the range can be anywhere in the sheet and not necessarily starting in A1. Also, my function only needed to merge cells that are in the same column. Spaces are also added where needed. The function then completes the merge of all cells without showing system messages. Thanks for starting me up on this code... Sub MergePlus() Application.DisplayAlerts = False For J = 0 To Selection.Cells.Count - 1 If J 0 Then MyJoin = MyJoin & " " & Selection.Range("A1").Offset(J, 0).Value Else MyJoin = MyJoin & Selection.Range("A1").Offset(J, 0).Value End If Next J Selection.Range("A1").Value = MyJoin MyJoin = "" Selection.Merge With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .MergeCells = True End With Application.DisplayAlerts = True End Sub ....Gaetan "Damon Longworth" wrote: Try something similar to: For j = 0 To Application.WorksheetFunction.CountA(Range("A:A")) - 1 c = 0 Do While Range("A1").Offset(j, c).Value < "" MyJoin = MyJoin & Range("A1").Offset(j, c).Value c = c + 1 Loop Range("A1").Offset(j, 0).Value = MyJoin MyJoin = "" Next j -- Damon Longworth 2007 Excel / Access User Conference London, England - Currently rescheduled St. Louis, Missouri - Oct 24-26, 2007 www.ExcelUserConference.com/ "Gaetan" wrote in message ... Hi, I have many files that contains cells in which sentences were split in multiple contiguous cells. As an example, I have "How are" in A1, "you doing" in A2 and "today?" in A3. I need to merge these 3 cells by keeping the content of A1, A2 and A3 into A1. There could be a variable quantity of cells I need to merge in this manner. This means, sometimes it can be 3, sometimes 4, and so on. I know some VBA code could help me do this and save me valuable time but I am unsure where to start with this. Can anyone help me? Thanks in advance! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging multiple cells with text
Sub ConCat_Cells()
Dim x As Range Dim y As Range Dim z As Range Dim w As String Dim sbuf As String On Error GoTo endit w = InputBox("Enter the Type of De-limiter Desired") Set z = Application.InputBox("Select Destination Cell", _ "Destination Cell", , , , , , 8) Application.SendKeys "+{F8}" Set x = Application.InputBox("Select Cells, Contiguous or Non-Contiguous", _ "Cells Selection", , , , , , 8) For Each y In x If Len(y.text) 0 Then sbuf = sbuf & y.text & w Next z = Left(sbuf, Len(sbuf) - 1) Exit Sub endit: MsgBox "Nothing Selected. Please try again." End Sub Gord Dibben MS Excel MVP On Mon, 27 Aug 2007 17:46:00 -0700, Gaetan wrote: Kevin, That would take me more time to enter this formula than to actually retype the text in the merged cells. Also, the amount of cells varies from time to time... it could be 3 at one point and 4 then other, or 5... thus preventing me from using the same formula in all cells. But thanks anyway... "Kevin B" wrote: Try a formula like this: =TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E2) The TRIM formula will remove any leading & trailing spaces and the &" "& places a literal space between each of the cell entries. If you don't need any spaced between cell entries use the following: =TRIM(A1&B1&C1&D1&E2) -- Kevin Backmann "Gaetan" wrote: Hi, I have many files that contains cells in which sentences were split in multiple contiguous cells. As an example, I have "How are" in A1, "you doing" in A2 and "today?" in A3. I need to merge these 3 cells by keeping the content of A1, A2 and A3 into A1. There could be a variable quantity of cells I need to merge in this manner. This means, sometimes it can be 3, sometimes 4, and so on. I know some VBA code could help me do this and save me valuable time but I am unsure where to start with this. Can anyone help me? Thanks in advance! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging multiple cells with text
For the kind of work I needed to do with the code, it's a bit slower to use
but your code gives the definite advantage of being able to select un-contiguous cells. I'll definately keep that for future use. Thanks Gord! "Gord Dibben" wrote: Sub ConCat_Cells() Dim x As Range Dim y As Range Dim z As Range Dim w As String Dim sbuf As String On Error GoTo endit w = InputBox("Enter the Type of De-limiter Desired") Set z = Application.InputBox("Select Destination Cell", _ "Destination Cell", , , , , , 8) Application.SendKeys "+{F8}" Set x = Application.InputBox("Select Cells, Contiguous or Non-Contiguous", _ "Cells Selection", , , , , , 8) For Each y In x If Len(y.text) 0 Then sbuf = sbuf & y.text & w Next z = Left(sbuf, Len(sbuf) - 1) Exit Sub endit: MsgBox "Nothing Selected. Please try again." End Sub Gord Dibben MS Excel MVP On Mon, 27 Aug 2007 17:46:00 -0700, Gaetan wrote: Kevin, That would take me more time to enter this formula than to actually retype the text in the merged cells. Also, the amount of cells varies from time to time... it could be 3 at one point and 4 then other, or 5... thus preventing me from using the same formula in all cells. But thanks anyway... "Kevin B" wrote: Try a formula like this: =TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E2) The TRIM formula will remove any leading & trailing spaces and the &" "& places a literal space between each of the cell entries. If you don't need any spaced between cell entries use the following: =TRIM(A1&B1&C1&D1&E2) -- Kevin Backmann "Gaetan" wrote: Hi, I have many files that contains cells in which sentences were split in multiple contiguous cells. As an example, I have "How are" in A1, "you doing" in A2 and "today?" in A3. I need to merge these 3 cells by keeping the content of A1, A2 and A3 into A1. There could be a variable quantity of cells I need to merge in this manner. This means, sometimes it can be 3, sometimes 4, and so on. I know some VBA code could help me do this and save me valuable time but I am unsure where to start with this. Can anyone help me? Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text from 2 Cells merging to another...Help | Excel Discussion (Misc queries) | |||
Merging multiple worksheets (selective cells) | Excel Discussion (Misc queries) | |||
Merging cells and text wrap | New Users to Excel | |||
WRAP a text line over several columns without merging cells | Excel Worksheet Functions | |||
Merging cells with text as one line | Excel Worksheet Functions |