Combine comma separated strings

Hi

I am trying to work out if there is an easy way to combine multiple comma separated strings to make one string. The only thing I can think of is to try and write some extremely long winded code that would split out to columns and then use if statements to put stuff back together as one.

As an example the original strings would look something like this.
1ABCD,,000001,,,Test,,Check,,,,,
1ABCD,,000001,Red,,,,,,,,,
1ABCD,,000001,,,Test,Test2,,,,,,

And the result would be.
1ABCD,,000001,Red,,Test,Test2,Check,,,,,

The number of commas could be different each time this is run but for the strings being compared the number of commas would always match. If anyone has any suggestions as to how this could be acheived that would be great.

Thanks in advance

Jim

Try:

Sub CombineStrings()
    Dim i As Integer
    Dim j As Integer
    Dim sIn() As String
    Dim sTemp() As String
    Dim sOut() As String
    Dim sOutput As String
    
    ReDim sIn(1 To 3)
    sIn(1) = "1ABCD,,000001,,,Test,,Check,,,,,"
    sIn(2) = "1ABCD,,000001,Red,,,,,,,,,"
    sIn(3) = "1ABCD,,000001,,,Test,Test2,,,,,,"
    
    For i = LBound(sIn) To UBound(sIn)
        sTemp = Split(sIn(i), ",")
        ReDim Preserve sOut(UBound(sTemp))
        For j = LBound(sTemp) To UBound(sTemp)
            If sOut(j) = "" Then sOut(j) = sTemp(j)
        Next j
    Next i
    sOutput = Join(sOut, ",")
    Debug.Print sOutput
End Sub

Happy to help. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You can also 'Add Reputation' to those who helped you. Thanks.