Concatenate
 

When using concatenate if I have cells that are empty in
that row how can I disregard including the empty cell,
without typing function in manually for each row ?

Example:
Col 1 Col 2 Col 3 Col 4 Col 5
Dan Dave Paul Sue

Function I use is
=CONCATENATE(A1,",",B1,",",C1,",",E1,",",)
Results I receive a Dan,,Dave,Paul, Sue
Like to see: Dan, Dave, Paul, Sue

Try in say, F1:

=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)," ",", ")

Copy F1 down

The above essentially uses TRIM to remove any extraneous "in-between" spaces
from the concat string first, then SUBSTITUTE will replace all the single
spaces: " " in between words with a comma-space: ", " to give the desired
result

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"JT" wrote in message
...
When using concatenate if I have cells that are empty in
that row how can I disregard including the empty cell,
without typing function in manually for each row ?

Example:
Col 1 Col 2 Col 3 Col 4 Col 5
Dan Dave Paul Sue

Function I use is
=CONCATENATE(A1,",",B1,",",C1,",",E1,",",)
Results I receive a Dan,,Dave,Paul, Sue
Like to see: Dan, Dave, Paul, Sue

Hi!

Try this:

=SUBSTITUTE(CONCATENATE
(A1,",",B1,",",C1,",",D1,",",E1),",,",",")

Biff

-----Original Message-----
When using concatenate if I have cells that are empty in
that row how can I disregard including the empty cell,
without typing function in manually for each row ?

Example:
Col 1 Col 2 Col 3 Col 4 Col 5
Dan Dave Paul Sue

Function I use is
=CONCATENATE(A1,",",B1,",",C1,",",E1,",",)
Results I receive a Dan,,Dave,Paul, Sue
Like to see: Dan, Dave, Paul, Sue
.

Hi!

Upon further testing I discovered that my formula only
works under certain conditions.

Use Max's formula, it's more robust.

Biff

-----Original Message-----
Hi!

Try this:

=SUBSTITUTE(CONCATENATE
(A1,",",B1,",",C1,",",D1,",",E1),",,",",")

Biff

-----Original Message-----
When using concatenate if I have cells that are empty in
that row how can I disregard including the empty cell,
without typing function in manually for each row ?

Example:
Col 1 Col 2 Col 3 Col 4 Col 5
Dan Dave Paul Sue

Function I use is
=CONCATENATE(A1,",",B1,",",C1,",",E1,",",)
Results I receive a Dan,,Dave,Paul, Sue
Like to see: Dan, Dave, Paul, Sue
.

.

JT

In addition to Max's solution you could use a User Defined Function that
ignores blank cells.

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 & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

usage is =ConCatRange(A1:E1) which will ignore D1


Gord Dibben Excel MVP

On Fri, 25 Feb 2005 19:27:02 -0800, "JT" wrote:

When using concatenate if I have cells that are empty in
that row how can I disregard including the empty cell,
without typing function in manually for each row ?

Example:
Col 1 Col 2 Col 3 Col 4 Col 5
Dan Dave Paul Sue

Function I use is
=CONCATENATE(A1,",",B1,",",C1,",",E1,",",)
Results I receive a Dan,,Dave,Paul, Sue
Like to see: Dan, Dave, Paul, Sue