Ignoring ranks when concatenating cells in Microsoft Excel

[New!life]

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.

[Go!pee]

As long as there are no embedded spaces in any of those columns (C:Z), you could use: =substitute(trim(c2&" "&d2&" "&e2&" "...&" "&z2)," ",", ")).

Public Function MultiCat( _
ByRef rRng As Excel.Range, _
Optional ByVal sDelim As String = "") _
As String
Dim rCell As Range
For Each rCell In rRng
if rcell.value = "" then
'do nothing
else
MultiCat = MultiCat & sDelim & rCell.Text
end if
Next rCell
if len(multicat) > 0 then
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
end if
End Function
And use it in a cell like:
=multicat(c2:z2,", ")

[Panop]

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: ", ". 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