Excal Concatination of cells that are not empty
 

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?

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

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

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

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 SpecialValues to get rid of the formulas.

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


Gord

On Wed, 19 Jan 2005 13:43:02 -0800, "orourksj"
wrote:

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

THanks, I must have forgotten about UDFs.

"Gord Dibben" wrote:

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 SpecialValues to get rid of the formulas.

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


Gord

On Wed, 19 Jan 2005 13:43:02 -0800, "orourksj"
wrote:

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