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