Combine text from multiple cells into one cell - =(A1&","&A2","&A3
This UDF 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
=concatrange(A1:A5)
Gord Dibben MS Excel MVP
On Sat, 25 Jul 2009 10:11:01 -0700, mh wrote:
I would like to combine the words from different cells into one cell -
sometimes 1 or 2 cells are left empty
Example
A1 A2 A3 A4 A5
Peter Simon Derek Empty Kent
combine into one cell marking all
=A1&","&A2&","&A3&","&A4&","&A5
Should say
Peter, Simon, Derek, Kent (without extra , or space for empty cell included)
Could somebody help me with formula for this? would be greatly appreciated
|