One way, using a User Defined Function:

Public Function CatNonBlanks( _
ByRef rRng As Excel.Range, _
Optional ByVal sDelim As String = "") As String
Dim rCell As Range
Dim sTemp As String
For Each rCell In rRng
If Not IsEmpty(rCell.Value) Then _
sTemp = sTemp & sDelim & rCell.Text
Next rCell
CatNonBlanks = Mid(sTemp, Len(sDelim) + 1, 32767)
End Function


If you're not familiar with UDFs, see

http://www.mvps.org/dmcritchie/excel/getstarted.htm


In article ,
PeterW wrote:

Hi

I am trying to concatenate a number of labels together separated by a
comma. However, if one of these cells is blank I end up with a number
of blanks together. For example A , B , C ,,,,,,,,G,H etc

As I want to remove these excess commas from the label I tried to use
the "Substitute" formula, as follows

=SUBSTITUTE(+F43&" , "&F44&" , "&F45&" , "&F46&" , "&F47&" , "&F48&" ,
"&F49&" , "&F50&" , "&F51&" , "&F52&" , "&F53&" , "&F54&" , "&F55&" ,
"&F56,",,","") ...... but it only removes every second comma

The real formula actually concatenates a lot mor cells than mentioned
in the above formula, so I can't use any "If" or "ISBlank" functions as
I will exceed the character limit of 256.

Does anyone have any other approach please?

Thanks in advance

Peter