I have two cells each with comma delimited values, I want to combine these into one cell

Question

I have two excel cells(A1 and B1) each contains comma delimited values in, I want to combine these values into one cell (C1), so that the first value from cell A1 is "linked" to the first value from cell B1 by a comma and so on for all the values For example: Cell A1 contains the following: B21:01,B22:02,B23:01,B25:01 Cell B1 contains the following: 1578,2758,10599,5478 Cell C1 should look like this: B21:01,1578,B22:02,2758,B23:01,10599,B25:01,5478

I have tried =concatenate(transpose(A1:A4)&",") which works well for 1 column, (if I transpose the values into a column) but cannot get it to work across multiple columns. Is there an easy way to do this?

Answer 1

Use this formula:

=TEXTJOIN(",",0,TRANSPOSE(TEXTSPLIT(A1&";"&B1,",",";")))

This is how it works:

Assuming A1 contains A,B,C,D,E and B1 contains 10,20,30,40,50:

1. A1 & ";" & B1 concatenates them, adding ; between them: A,B,C,D,E;10,20,30,40,50

2. TEXTSPLIT((that),",",";") expands them into a table:

   A	B	C	D	E
   10	20	30	40	50

3. TRANSPOSE((that)) transposes that table:

   A	10
   B	20
   C	30
   D	40
   E	50

4. TEXTJOIN(",",0,(that)) goes row-by-row, cell-by-cell over that table and joins up the contents of the cells, inserting , between them: A,10,B,20,C,30,D,40,E,50

Answer 2

Are you willing to use VBA? If so, this function will do what you want:

Function pair(ByVal c1 As Range, ByVal c2 As Range, ByVal separator As String) As String
    Dim arr1 As Variant, arr2 As Variant
    arr1 = Split(c1.Value, separator)
    arr2 = Split(c2.Value, separator)
    
    For i = 0 To WorksheetFunction.Min(UBound(arr1), UBound(arr2))
        pair = pair & arr1(i) & separator & arr2(i) & separator
    Next i
    pair = Left(pair, Len(pair) - Len(separator))
End Function

You can use it in your workbook like so (just replace the semicolons with commas. My language settings use semicolons) :

edit: This function will take the smaller number of elements between the two cells. So if there are 4 values in cell 1 and 3 values in cell 2, it will only make 3 pairs and ignore the 4th value in cell 1. Depending on whether or not this is desirable for you, you might need to modify the function.

edit2: and here is a version which strips decimals from the values in second cell:

Function pair(ByVal c1 As range, ByVal c2 As range, ByVal separator As String) As String
    Dim arr1 As Variant, arr2 As Variant
    arr1 = Split(c1.Value, separator)
    arr2 = Split(c2.Value, separator)
    
    For i = 0 To WorksheetFunction.Min(UBound(arr1), UBound(arr2))
        pair = pair & arr1(i) & separator & Split(arr2(i), ".")(0) & separator
    Next i
    pair = Left(pair, Len(pair) - Len(separator))
End Function