Is it possible to concate acroos a rage ignoring any blank values in cells,

for example I wold like to concate A1 to A6 but if there is no value in A4
ignore that cell

Thanks

Nigel,

Either your not explaining correctly or I'm not understanding because
concatenate ignores empty cells. That's not strictly true but it has the same
effect because it simply concatenates nothing from an empty cell.

Try the formula below and you will only see values from populated cells

=CONCATENATE(A1,A2,A3,A4,A5,A6)

Mike

"Nigel" wrote:

Is it possible to concate acroos a rage ignoring any blank values in cells,

for example I wold like to concate A1 to A6 but if there is no value in A4
ignore that cell

Thanks

If you want these values de-limited in some way then blank cells become a
factor.

Otherwise, as others have pointed out, it doesn't matter.

Maybe this UDF is what you want?

Function ConCatRange22(CellBlock As Range, Optional Delim As String = "") _
As String
'entered as =concatrange22(a1:a10,"|") desired delimiter between quotes
Dim Cell As Range
Dim sbuf As String

For Each Cell In CellBlock.Cells
If Cell.text < "" Then
sbuf = sbuf & Cell.text & Delim
End If
Next Cell

ConCatRange22 = Left(sbuf, Len(sbuf) - Len(Delim))

End Function


Gord Dibben MS Excel MVP

On Tue, 16 Jun 2009 12:20:01 -0700, Nigel wrote:

Is it possible to concate acroos a rage ignoring any blank values in cells,

for example I wold like to concate A1 to A6 but if there is no value in A4
ignore that cell

Thanks