Concatenate the ranges using this UDF which ignores blank cells so you don't
get the extra spaces and commas when data is missing.

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) - 1)
End Function


Gord Dibben MS Excel MVP

On Mon, 01 Jun 2009 20:08:35 -0500, StargateFan
wrote:

I was wondering how to clean up a couple of columns. I have
concatenated two fields together that had blank cells in some places.
The results I got after copy-pasting the concatenates formulas into
values show up in these three ways, the first one being okay, the
other two not:



Address1, Address2
(okay)

, Address2
(not okay, need to get rid of comma and space _before_ text)

Address1,
(also not okay, need to get rid of comma and space _after_ the text).



Is there an easier, quicker way to clean up the entries of these
commas and spaces where they're not needed vs. doing the clean up
manually?

Thanks! :oD