How to split and distribute columns like "A,B,C" and "X,Y,Z" into "A,X,B,Y,C,Z"?

Question

so this seems tricky to me but i have a set of data that need to be split into alternating comlumns here is what i mean is in the example below i split column A to C E and G ect depending on how many sets of data are in the cell seperated my a comma and then seperate comlumn B into the alternating comlumns D F and H ect ect

    | Column A | Column B | Column C | Column D | Column E | Column F | Column G |Column  H |
    | A, B, C  | X, Y, Z  | A        | X        | B        | Y        | C        | Z        |

somthing like above and my ultimate goal is to achive

   |     Column J    |
   |A, X, B, Y, C, Z |

please if anyone can help it is much appreciated

Answer 1

If you're willing to use VBA, it would be a relatively simply solution. Yes, it uses VBA, but I would consider it easier to follow than the workaround you would have to do with named ranges. The code is given below. You would call this function in your worksheet with a function like =BlendCells(", ",A2,B2). The first parameter is strDelimiter which allows you to input how the text is separated. In your example, that would be a comma and a space. The next two paramaters are the two cells that you want to blend.

Option Explicit

Public Function BlendCells(strDelimiter As String, Range1 As Range, Range2 As Range) As String

    'Declarations
    Dim arr1() As String
    Dim arr2() As String
    Dim i As Long

    'Setup each array
    arr1 = Split(Range1.Value, strDelimiter)
    arr2 = Split(Range2.Value, strDelimiter)

    'Blend the text
    For i = Application.Min(LBound(arr1), LBound(arr2)) To Application.Max(UBound(arr1), UBound(arr2))
        If i <= UBound(arr1) Then BlendCells = BlendCells & arr1(i) & strDelimiter
        If i <= UBound(arr2) Then BlendCells = BlendCells & arr2(i) & strDelimiter
    Next

    'Trim the results
    BlendCells = Left(BlendCells, Len(BlendCells) - Len(strDelimiter))

End Function

Answer 2

To get the sub-strings into the indivdual columns, you can use:

C1:  =INDEX(TRIM(MID(SUBSTITUTE(OFFSET($A$1,0,MOD(COLUMNS($A:A)+1,2)),",",REPT(" ",99)),{1,99,198},99)),MOD(COLUMNS($A:A)-1,3)+1)

and fill left to I1.

Alternatively, you could just use the Data/Text to Columns Tool with the comma as a delimiter.

To get the final result, you could either concatenate the individual columns:

=B1 & "," & C1 & "," & D1 & "," & E1 & "," & F1 & "," & G1 & "," & H1 & "," & I1

or, if you really don't need the individual columns, you can use a single formula:

J1: =CONCATENATE(
INDEX(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),{1,99,198},99)),1),",",
INDEX(TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",99)),{1,99,198},99)),1),",",
INDEX(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),{1,99,198},99)),2),",",
INDEX(TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",99)),{1,99,198},99)),2),",",
INDEX(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),{1,99,198},99)),3),",",
INDEX(TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",99)),{1,99,198},99)),3))

If the number of segments is more than three, merely extend the array constant and the concatenation formula. If the number of segments is variable, a VBA solution would be simpler depending on exactly what you want; the nature of the data, the location of the output, etc.

Answer 3

This is no easy task if you want to implement it without VBA.

One possible solution is to use a couple of array formulas inside named ranges:

• select any cell in the 1st row
• open Formulas ribbon tab > Name Manager dialog (Ctrl+F3)
• insert a New... named range for each of the following formulas:
  • copy&paste each row into the Name: field of the New Name dialog, then cut out the formula part and paste it into Refers to: field

Name          Refers to

separator     =","
special       ="^"
enum          =ROW($1:$10)
each_left_A   =IFERROR(FIND(special, SUBSTITUTE($A1, separator, special, enum)), 999)
each_left_B   =IFERROR(FIND(special, SUBSTITUTE($B1, separator, special, enum)), 999)
each_right_A  =IFERROR(FIND(special, SUBSTITUTE($A1, separator, special, enum + 1)), 999)
each_right_B  =IFERROR(FIND(special, SUBSTITUTE($B1, separator, special, enum + 1)), 999)
nth_str       =MID($A1, each_left_A, each_right_A - each_left_A) & MID($B1, each_left_B, each_right_B - each_left_B)
space         =IF(MID(nth_str, 2, 1)=" ", " ", "")
first_str     =LEFT($A1, each_left_A) & space & LEFT($B1, each_left_B - 1)

• then enter one of the following formulas into the output column (it is itself not an array formula, so confirm just by ENTER, you can repeat the INDEX as many times as required - but at most enum times, extra indexes will just produce empty strings, but there will be a performance penalty if your table is large):

  =first_str & INDEX(nth_str, 1) & INDEX(nth_str, 2)
  =first_str & INDEX(nth_str, 1) & INDEX(nth_str, 2) & INDEX(nth_str, 3) & INDEX(nth_str, 4) & INDEX(nth_str, 5) & INDEX(nth_str, 6) & INDEX(nth_str, 7) & INDEX(nth_str, 8) & INDEX(nth_str, 9) & INDEX(nth_str, 10)
  

  Like this (printscreen from Excel 2010):

Please let me know if detailed explanation is required of all / some formulas.
See also Excel vocabulary to find solutions faster.