Merging multiple cells with text

[Guest]

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!

 

[Pete_UK]

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

 

[Guest]

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)

 

[Guest]

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.

 

[Guest]

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...

 

[Damon Longworth]

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/


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!

 

[Guest]

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

 

[Guest]

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

 

[Gord Dibben]

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

 

[Guest]

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!