Concatenating cells but excluding blanks
 

Hello,

I am trying to create a result field, concatenating populated cells from the
previous 12 columns on that line, but excluding blank cells and putting a *
delimiting character between each instance - please find below a 4 column
example.

ID 1 2 3 4 Result
Z A C D A*C*D
Y B C B*C
X A B D A*B*D

Each of the 10,000 lines of the spreadsheet is different - there are at
least 5 blank cells on each line

Any help gratefully received. I am working in Excel 2007

Many thanks.

Bob

Concatenating cells but excluding blanks
 

Try this:
http://img690.imageshack.us/img690/5826/nonamee.png
Micky


"Bob Freeman" wrote:

Hello,

I am trying to create a result field, concatenating populated cells from the
previous 12 columns on that line, but excluding blank cells and putting a *
delimiting character between each instance - please find below a 4 column
example.

ID 1 2 3 4 Result
Z A C D A*C*D
Y B C B*C
X A B D A*B*D

Each of the 10,000 lines of the spreadsheet is different - there are at
least 5 blank cells on each line

Any help gratefully received. I am working in Excel 2007

Many thanks.

Bob

Concatenating cells but excluding blanks
 

This UDF will concatenate a range and exclude blanks.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
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) - 2)
End Function

=concatrange(range)


Gord Dibben MS Excel MVP

On Tue, 12 Jan 2010 05:23:01 -0800, Bob Freeman
wrote:

Hello,

I am trying to create a result field, concatenating populated cells from the
previous 12 columns on that line, but excluding blank cells and putting a *
delimiting character between each instance - please find below a 4 column
example.

ID 1 2 3 4 Result
Z A C D A*C*D
Y B C B*C
X A B D A*B*D

Each of the 10,000 lines of the spreadsheet is different - there are at
least 5 blank cells on each line

Any help gratefully received. I am working in Excel 2007

Many thanks.

Bob

Concatenating cells but excluding blanks
 

I assume you meant:
ConCatRange = Left(sbuf, Len(sbuf) - 1)
Micky


"Gord Dibben" wrote:

This UDF will concatenate a range and exclude blanks.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
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) - 2)
End Function

=concatrange(range)


Gord Dibben MS Excel MVP

On Tue, 12 Jan 2010 05:23:01 -0800, Bob Freeman
wrote:

Hello,

I am trying to create a result field, concatenating populated cells from the
previous 12 columns on that line, but excluding blank cells and putting a *
delimiting character between each instance - please find below a 4 column
example.

ID 1 2 3 4 Result
Z A C D A*C*D
Y B C B*C
X A B D A*B*D

Each of the 10,000 lines of the spreadsheet is different - there are at
least 5 blank cells on each line

Any help gratefully received. I am working in Excel 2007

Many thanks.

Bob

.

Concatenating cells but excluding blanks
 

I meant ConCatRange = Left(sbuf, Len(sbuf) - 2)

Try it using ConCatRange = Left(sbuf, Len(sbuf) - 1)

See the difference?


Gord

On Wed, 13 Jan 2010 00:21:01 -0800, ????? (????) ?????
<micky-a*at*tapuz.co.il wrote:

I assume you meant:

Micky


"Gord Dibben" wrote:

This UDF will concatenate a range and exclude blanks.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
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) - 2)
End Function

=concatrange(range)


Gord Dibben MS Excel MVP

On Tue, 12 Jan 2010 05:23:01 -0800, Bob Freeman
wrote:

Hello,

I am trying to create a result field, concatenating populated cells from the
previous 12 columns on that line, but excluding blank cells and putting a *
delimiting character between each instance - please find below a 4 column
example.

ID 1 2 3 4 Result
Z A C D A*C*D
Y B C B*C
X A B D A*B*D

Each of the 10,000 lines of the spreadsheet is different - there are at
least 5 blank cells on each line

Any help gratefully received. I am working in Excel 2007

Many thanks.

Bob

.