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!