Comma's btw numbers

[Maxwell]

I am concatenating 6 cells together A1 -F1. Each cell
could have a number in there or a blank.
If there are more than 1 cell out of the 6 cells that have
numbers, I want to put a comma between the numbers.

It could look like this[example]...(1,2,3,4,5,6) or
(3,5,6) or (1,5) or (1,2,5) or any combination of these...

BUT I dont want it to turn out like this....
(,1) or (,3,5). Don't like the commas in front like that.

Is there a way in format or a simple formula that will do
this for me?

help

 

[Jason Morin]

Press Ctrl+F3 and give the name "comma" (no quotes) to:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
($A$1&","&$B$1&","&$C$1&","&$D$1&","&$E$1&","&$F$1,",,",",
"),",,",","),",,",",")

Now use this in an open cell:

=MID(comma,1+(LEFT(comma)=",")*1,LEN(comma)-(RIGHT(comma)
=",")*1-(LEFT(comma)=",")*1)

HTH
Jason
Atlanta, GA

 

[dave]

try in g1:
=CONCATENATE(IF(A1=0,"",A1&","),IF(B1=0,"",B1&","),IF
(C1=0,"",C1&","),IF(D1=0,"",D1&","),IF(E1=0,"",E1&","),F1)

2 3 5 7 8 2,3,5,7,8
1 4 5 7 1,4,5,7

hth,
Dave

 

[dave]

my first answer was incomplete sorry. Here is the
complete answer.

=CONCATENATE(IF(A1=0,"",A1&IF(SUM(B1:F1)>0,",","")),IF
(B1=0,"",B1&IF(SUM(C1:F1)>0,",","")),IF(C1=0,"",C1&IF(SUM
(D1:F1)>0,",","")),IF(D1=0,"",D1&IF(SUM(E1:F1)

 

[Maxwell]

This one gives me a comma at the end like (1,2,4,) Not
good

-----Original Message-----
try in g1:
=CONCATENATE(IF(A1=0,"",A1&","),IF(B1=0,"",B1&","),IF
(C1=0,"",C1&","),IF(D1=0,"",D1&","),IF(E1=0,"",E1&","),F1)

2 3 5 7 8 2,3,5,7,8
1 4 5 7 1,4,5,7

hth,
Dave

-----Original Message-----
I am concatenating 6 cells together A1 -F1. Each cell
could have a number in there or a blank.
If there are more than 1 cell out of the 6 cells that have
numbers, I want to put a comma between the numbers.

It could look like this[example]...(1,2,3,4,5,6) or
(3,5,6) or (1,5) or (1,2,5) or any combination of these...

BUT I dont want it to turn out like this....
(,1) or (,3,5). Don't like the commas in front like that.

Is there a way in format or a simple formula that will do
this for me?

help


.

.

 

[Dave Peterson]

One more:

=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1)," ",",")

I am concatenating 6 cells together A1 -F1. Each cell
could have a number in there or a blank.
If there are more than 1 cell out of the 6 cells that have
numbers, I want to put a comma between the numbers.

It could look like this[example]...(1,2,3,4,5,6) or
(3,5,6) or (1,5) or (1,2,5) or any combination of these...

BUT I dont want it to turn out like this....
(,1) or (,3,5). Don't like the commas in front like that.

Is there a way in format or a simple formula that will do
this for me?

help