Concatenating cells but excluding blanks

[Bob Freeman]

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

 

[מיכ×ל (מיקי) ×בידן]

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

 

[Gord Dibben]

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

 

[מיכ×ל (מיקי) ×בידן]

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

 

[Gord Dibben]

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

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

See the difference?


Gord

 

[Katherine Berchtold]

Hi Gord,

I used the macro you posted to concatenate a range of data and exclude the blanks.

This works great, the only problem is that it cuts off the last letter of the last word.

Please help!

Thank you.

Katherine

 

[Gord Dibben]

Micky was correct..............change the -2 to -1 in this case.


Gord