1

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

1

3 Answers 3

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
2
  • Application.Min(LBound(arr1), LBound(arr2)) is a bit excessive way to say 0, otherwise works fine
    – Aprillion
    Jan 16, 2015 at 11:06
  • 1
    In this case, it's a very verbose 0 but I've worked with code that uses Option Base 1 so I got in the habit of using the long but robust method. The Min function is unnecessary in this case, though, since I just defined both arrays in the same way so they'd both have the same lower bound. For the sake of the performance improvement, it can be reduced to just LBound(arr1) Jan 17, 2015 at 21:44
1

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.

2
  • very nice trick for constant number of items to split & merge
    – Aprillion
    Jan 16, 2015 at 10:59
  • @Aprillion Thanks. One could extend it by using a ROW(INDIRECT(... construct to form the arrays; and COLUMNS(...)/2 for the column_num in the INDEX function, but the complexities of concatenating variable sized ranges in Excel without VBA would make me choose VBA in that situation (unless someone were paying me :-)) Jan 16, 2015 at 11:32
0

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):

merge texts printscreen

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.