Thanks, That is great stuff, I have about 5000 rows to concatenate. How can I
select a range of input cells and range of corresponding output cells. I also
have a delimiter on the resultant cell that needs to be removed.

"Gord Dibben" wrote:

You could use a macro where you choose the de-limiter if required and select
the range using mouse....

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

OR a UDF.......

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & ","
'note the comma could be deleted if you just want a space
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is: =ConCatRange(A1:A30)


Gord Dibben Excel MVP

On Wed, 19 Jan 2005 11:24:15 -0800, "Jason Morin"
wrote:

If they are empty, then there's nothing to concatenate
and thus nothing to worry about. To concatenate a range
of cells, try a UDF like this one from J.E. McGimpsey:

http://tinyurl.com/5wvz3

HTH
Jason
Atlanta, GA

-----Original Message-----
I am trying to concatinate a row of 30 cells. Some of
these are empty or
blank. I want to combine these 30 cells where they are
not blank. I have
tried several functions, however the limit is 7
functions in one cell. How
can I complete this task?
.