VBA code for dynamic concatenate

Hey All,

Need your insight and support on the following.
I am creating a product catalog and am encountering some challenges along the way (VBA wise). One of the challenges is product color.
I have a database of items (flat database of some 1000 items/lines X 20 columns) and in each line, amongst other pieces of data, there are colors
Column B contains Color 1 (Column C will have the available stock)
Column D can contain Color 2 (Column E will have the available stock)
Column F can contain Color 3 (Column G will have the available stock)
Column H can contain Color 4 (Column I will have the available stock)
Column J can contain Color 5 (Column K will have the available stock)

So cell by cell (in a row) – this is what you see (for example): Black 50 White 40 Green 75 Yellow 30 Brown 48

The thing is, sometimes there is only one color and sometimes – there are up to 5 (the colors change all the time).
What I want to do is concatenate (in column L) all the COLORS of each record, with a separator : Color 1 | Color 2 | Color 3 | Color 4 | Color 5. (i.e. Black | White | Green | Yellow | Brown)

Obviously, if there is only one color or two, I don't want the "|" appearing if there are no more colors…

So, basically, I am looking for a "dynamic concatenate" code.
I know how to count the number of colors that are listed per line (I simply count the non-empty cells between column B and K, and divide by 2…).
Now I need to create this dynamic concatenate (with the separators) for the colors in each specific line….
I can't think of reasonable and fast way to do it, but I am pretty sure that this is something that can be achieved with an Array.

Any help will be highly appreciated

Re: VBA code for dynamic concatenate

See attached sample

Re: VBA code for dynamic concatenate

Quote from StephenR;775381

Here's a simple way, using a custom function, e.g. =Oz(B3:K3) in L3

Code

Function Oz(r As Range) As String


Dim r1 As Range


For Each r1 In r
    If Not IsNumeric(r1) Then
        Oz = Oz & " | " & r1
    End If
Next r1


Oz = Mid(Oz, 4)


End Function

Display More

Thanks very much for the swift reply !!!!!!
I need to apologize, because I did not emphasis that I am looking for a "procedure" type of VBA rather than a function.
The issue of the colors is one of many I am trying to address as part of my project of creating a product catalog out of a flat database.
I am working on a code that will loops through the database, import pictures, rearrange the data in a "nice" visual manner and also list the colors of certain products beneath relevant picture.

I will try to use the logic of the function you wrote and incorporate it as part an the code I am writing. Hope this will work out

Thanks again :thumbcoo: