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


--
PeterW
------------------------------------------------------------------------
PeterW's Profile: http://www.excelforum.com/member.php...fo&userid=6496
View this thread: http://www.excelforum.com/showthread...hreadid=502775

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

Beautiful ... that works


--
PeterW
------------------------------------------------------------------------
PeterW's Profile: http://www.excelforum.com/member.php...fo&userid=6496
View this thread: http://www.excelforum.com/showthread...hreadid=502775

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)," "," , ")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"PeterW" wrote in
message ...

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


--
PeterW
------------------------------------------------------------------------
PeterW's Profile:
http://www.excelforum.com/member.php...fo&userid=6496
View this thread: http://www.excelforum.com/showthread...hreadid=502775