Thanks so much for your help. I wasn't aware of SUBSTITUTE and TRIM before.

"Max" wrote:

One way is use TRIM on the individual cell concats - TRIM will remove all
extraneous white spaces except the single space in-between values, then use
SUBSTITUTE to replace these single spaces: " " with a comma-space, ie: ", "

Try this ..

Insert a new col B, so your 24 cols would now be cols C to Z

Then place in B2 something like this
(assuming concats only for the 1st 4 cols: C to F):
=SUBSTITUTE(TRIM(C2&" "&D2&" "&E2&" "&F2)," ",", ")
Copy B2 down to return the desired neat results

Extend the part within the TRIM(...) to suit
Your final expression in B2 should look something like this:
=SUBSTITUTE(TRIM(C2&" "&D2&" "&E2&" "&F2& ... &" "&Z2)," ",", ")

Note that it's easier & neater to use the ampersand operator: & for the cell
concats, instead of CONCATENATE
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"NatChat" wrote:
Hi there,

I have run out of ideas on this one and now I've lost my fresh morning mind,
so any help would be much appreciated.

I have a list of garments and each style has a different number of sizes in
its size range from a minimum of 1 available size to a maximum of 24
available sizes, so I have 24 columns for sizes.

I want to concatenate the available sizes for each style separated by a
comma and a space.

A simplified version of the data as it currently stands is like this:

A B C D E etc.....out to 24 columns
1 Stock Item Name Available Sizes
2 Stock Item A 8 10 12 14
3 Stock Item B S M L
4 Stock Item C OS

and the result I want in one column (single cell) is:

A B
1 Stock Item Name Available Sizes
2 Stock Item A 8, 10, 12, 14
3 Stock Item B S, M, L
4 Stock Item C OS

The problem when I use concatenate or "&" across the 24 columns is that I
end up with commas and spaces after the available sizes for all of the
remaining blank cells.

I can't use IF because I can't embed that many IF statements in the one
formula.

Hope this makes sense. Thanks.