Excal Concatination of cells that are not empty

[Guest]

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?

 

[Jason Morin]

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

 

[Gord Dibben]

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

 

[Guest]

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]

I would use the UDF for that.

Assuming you have 30 columns per row.

In Column 31(AE) enter =ConCatRange(A1:AD1)

Double-click on the fill handle to copy down to row 5000 if you have data in
all cells in Column AD.

Otherwise drag/copy down or copy AE1 and type AE2:AE5000 in the namebox then
<ENTER> <ENTER>

Copy and Paste Special>Values to get rid of the formulas.

Don't know what you mean by "delimiter on the resultant cell that needs to be
removed."


Gord

 

[Guest]

THanks, I must have forgotten about UDFs.

I would use the UDF for that.

Assuming you have 30 columns per row.

In Column 31(AE) enter =ConCatRange(A1:AD1)

Double-click on the fill handle to copy down to row 5000 if you have data in
all cells in Column AD.

Otherwise drag/copy down or copy AE1 and type AE2:AE5000 in the namebox then
<ENTER> <ENTER>

Copy and Paste Special>Values to get rid of the formulas.

Don't know what you mean by "delimiter on the resultant cell that needs to be
removed."


Gord