Concatenating cells but excluding blanks

  • Thread starter Thread starter Bob Freeman
  • Start date Start date
B

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
 
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
 
I meant ConCatRange = Left(sbuf, Len(sbuf) - 2)

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

See the difference?


Gord
 
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
 
Back
Top