Concatenate and remove blanks

[PeterW]

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

 

[JE McGimpsey]

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

 

[PeterW]

Beautiful ... that works

 

[Max]

An alternative to try, which could neatly remove excess commas
for any blank cells within the concat range would be something like:

=SUBSTITUTE(TRIM(F43&" "&F44&" "&F45&" "&F46&" "&F47&" "&F48&" "
&F49&" "&F50&" "&F51&" "&F52&" "&F53&" "&F54&" "&F55&" "&F56)," "," , ")