software

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 quot;Substitutequot; formula, as follows

=SUBSTITUTE( F43amp;quot; , quot;amp;F44amp;quot; , quot;amp;F45amp;quot; , quot;amp;F46amp;quot; , quot;amp;F47amp;quot; , quot;amp;F48amp;quot; ,
quot;amp;F49amp;quot; , quot;amp;F50amp;quot; , quot;amp;F51amp;quot; , quot;amp;F52amp;quot; , quot;amp;F53amp;quot; , quot;amp;F54amp;quot; , quot;amp;F55amp;quot; ,
quot;amp;F56,quot;,,quot;,quot;quot;) ...... 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 quot;Ifquot; or quot;ISBlankquot; 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: www.excelforum.com/member.php...foamp;userid=6496
View this thread: www.excelforum.com/showthread...hreadid=502775One way, using a User Defined Function:

Public Function CatNonBlanks( _
ByRef rRng As Excel.Range, _
Optional ByVal sDelim As String = quot;quot;) As String
Dim rCell As Range
Dim sTemp As String
For Each rCell In rRng
If Not IsEmpty(rCell.Value) Then _
sTemp = sTemp amp; sDelim amp; rCell.Text
Next rCell
CatNonBlanks = Mid(sTemp, Len(sDelim) 1, 32767)
End FunctionIf you're not familiar with UDFs, see

www.mvps.org/dmcritchie/excel/getstarted.htmIn article gt;,
PeterW gt; wrote:

gt; Hi
gt;
gt; I am trying to concatenate a number of labels together separated by a
gt; comma. However, if one of these cells is blank I end up with a number
gt; of blanks together. For example A , B , C ,,,,,,,,G,H etc
gt;
gt; As I want to remove these excess commas from the label I tried to use
gt; the quot;Substitutequot; formula, as follows
gt;
gt; =SUBSTITUTE( F43amp;quot; , quot;amp;F44amp;quot; , quot;amp;F45amp;quot; , quot;amp;F46amp;quot; , quot;amp;F47amp;quot; , quot;amp;F48amp;quot; ,
gt; quot;amp;F49amp;quot; , quot;amp;F50amp;quot; , quot;amp;F51amp;quot; , quot;amp;F52amp;quot; , quot;amp;F53amp;quot; , quot;amp;F54amp;quot; , quot;amp;F55amp;quot; ,
gt; quot;amp;F56,quot;,,quot;,quot;quot;) ...... but it only removes every second comma
gt;
gt; The real formula actually concatenates a lot mor cells than mentioned
gt; in the above formula, so I can't use any quot;Ifquot; or quot;ISBlankquot; functions as
gt; I will exceed the character limit of 256.
gt;
gt; Does anyone have any other approach please?
gt;
gt; Thanks in advance
gt;
gt; Peter


Beautiful ... that works--
PeterW
------------------------------------------------------------------------
PeterW's Profile: www.excelforum.com/member.php...foamp;userid=6496
View this thread: www.excelforum.com/showthread...hreadid=502775An alternative to try, which could neatly remove excess commas
for any blank cells within the concat range would be something like:

=SUBSTITUTE(TRIM(F43amp;quot; quot;amp;F44amp;quot; quot;amp;F45amp;quot; quot;amp;F46amp;quot; quot;amp;F47amp;quot; quot;amp;F48amp;quot; quot;
amp;F49amp;quot; quot;amp;F50amp;quot; quot;amp;F51amp;quot; quot;amp;F52amp;quot; quot;amp;F53amp;quot; quot;amp;F54amp;quot; quot;amp;F55amp;quot; quot;amp;F56),quot; quot;,quot; , quot;)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;PeterWquot; gt; wrote in
message ...
gt;
gt; Hi
gt;
gt; I am trying to concatenate a number of labels together separated by a
gt; comma. However, if one of these cells is blank I end up with a number
gt; of blanks together. For example A , B , C ,,,,,,,,G,H etc
gt;
gt; As I want to remove these excess commas from the label I tried to use
gt; the quot;Substitutequot; formula, as follows
gt;
gt; =SUBSTITUTE( F43amp;quot; , quot;amp;F44amp;quot; , quot;amp;F45amp;quot; , quot;amp;F46amp;quot; , quot;amp;F47amp;quot; , quot;amp;F48amp;quot; ,
gt; quot;amp;F49amp;quot; , quot;amp;F50amp;quot; , quot;amp;F51amp;quot; , quot;amp;F52amp;quot; , quot;amp;F53amp;quot; , quot;amp;F54amp;quot; , quot;amp;F55amp;quot; ,
gt; quot;amp;F56,quot;,,quot;,quot;quot;) ...... but it only removes every second comma
gt;
gt; The real formula actually concatenates a lot mor cells than mentioned
gt; in the above formula, so I can't use any quot;Ifquot; or quot;ISBlankquot; functions as
gt; I will exceed the character limit of 256.
gt;
gt; Does anyone have any other approach please?
gt;
gt; Thanks in advance
gt;
gt; Peter
gt;
gt;
gt; --
gt; PeterW
gt; ------------------------------------------------------------------------
gt; PeterW's Profile:
www.excelforum.com/member.php...foamp;userid=6496
gt; View this thread: www.excelforum.com/showthread...hreadid=502775
gt;