How to Concatenate Arrays in Excel (With 2 Conditions)

While working in Excel, we sometimes need to combine texts located in several rows and columns. In this case, the processes to concatenate these arrays are very useful for multipurpose use. In this article, we will discuss how to concatenate arrays in excel with 2 conditions.

2 Conditions to Concatenate Arrays in Excel

The procedure of how to concatenate arrays can be done for both single arrays and multiple arrays. Following we will describe both of them with various excel formulas.

1. Concatenate Single Array in Excel

In this section, we will concatenate a single array from rows of a text string. In this sample dataset, we have a text string in the cell range B5:B13.

Now, let us follow the methods below to concatenate this into a single cell.

1.1. Combine CONCATENATE & TRANSPOSE Functions

We can easily combine text string by fusing the CONCATENATE and TRANSPOSE functions in Excel. To do this, follow the steps below.

First, select cell C8 and type this formula.

=TRANSPOSE(B5:B13)&” “

Then, select the whole formula and press F9 on your keyboard to convert the formula into values like this.

Next, add the CONCATENATE function at the beginning and complete the formula as follows.

=CONCATENATE("A ","computer ","is ","like ","a ","bicycle ","for ","your ","mind ")

Note: Microsoft has changed how array formulas work in the version of Excel 365. On older versions, we needed to press Ctrl + Shift + Enter to calculate an array formula.

Finally, press Enter and you will see the required output.

In this formula, the TRANSPOSE function converts the vertical cell range B5:B13 into a horizontal one. Following, the CONCATENATE function combines them and converts them to a single line.

1.2. Use Fill Justify Command in Excel

In Microsoft Excel, Fill Justify is a rare but very useful command for concatenating. Let’s see how it works.

In the beginning, select cell range B5:B13.

Then, go to the Home tab and click on Fill under the Editing group.

Following, select Justify from the drop-down menu.

That’s it, you will successfully get the concatenated array from the single array.

1.3. Apply TEXTJOIN Function

The TEXTJOIN function is very beneficial to concatenate a single array. For this simply apply the steps below.

First, select cell C9.
In this cell, insert this formula.

=TEXTJOIN(" ",TRUE,B5:B13)

Afterward, press Enter.
Finally, you will successfully concatenate an array like this.

Here, the TEXTJOIN function combines the text string into a single line. To get them with separators, we provided a space between two Quotation Marks (“)in the formula. Lastly, type TRUE along with the cell range B5:B13 to ignore empty cells from the dataset.

1.4. Concatenate with Power Query

Another useful method for concatenating arrays with Power Query in Excel. To do the task, go through the following process carefully.

In the beginning, select cell range B5:B13.
Then, go to the Data tab and select From Table/Range under the Get & Transform Data.

Next, you will see the Power Query Editor window.
In this window, select the column and go to the Transform tab.
Here, select Transpose from the Table group.

Now, select all the separated columns in the window and right-click on any of them.
Afterward, click on Merge Columns.

Following, choose Space as the Separator in the Merge Columns dialogue box.
Along with it, type Concatenated Array in the New column name section.

Lastly, select Close & Load from the Home tab.

Finally, you will concatenate the array in a new worksheet.

1.5. Apply Excel VBA Code

In this last process, we will concatenate a single array with a VBA code. Following is the process to perform this.

Firstly, go to the Developer tab and select Visual Basic from the Code group.

Secondly, select Module from the Insert section in the Visual Basic window.

Thirdly, insert this code in the blank page.

Sub ConcatenateArray()
 Dim rg As Range
 Dim x As String
 For Each rg In Selection
 x = x & rg & " "
 Next rg
 Range("C9").Value = Trim(x)
 End Sub

Note: In this code, the cell reference C9 is the location where you want to get the output. This cell reference is variable according to your own dataset.

Following, save the code and close the window.
Now, select the cell range B5:B13.
Then, again go to the Developer tab and click on Macros.

Next, you will see the Macros window with the Macro name.
Here, click on Run to operate the code for the selected cells.

That’s it, you will finally see the concatenated array like this.

2. Concatenate Multiple Arrays with Excel Formulas

Now, we will go through another condition where we will concatenate multiple arrays using excel formulas. So without any delay, let’s go for it.

2.1. Apply CHOOSE Function

The CHOOSE function is a very useful one for concatenating.

First, create a sample dataset with 5 City names and their Post Codes like the image below.

Now, create a new table where we will get the output.

Then, insert this formula in cell F5.

=CHOOSE({1,2},B5:B9,D5:D9)

Finally, hit Enter and you will get the multiple arrays all at once.

In the formula, the CHOOSE function returns the values from the cell range B5:B9 and D5:D9. Along with this, they are positioned as 1 and 2 respectively under curly brackets.

2.2. Use Excel VSTACK Function

We can use the VSTACK function to arrange multiple arrays in a vertical format. To do this, here is a dataset with 6 Product names and Quantities in two different tables.

First, create a new table where you wish to get the output.

Then, type this formula in cell B10.

=VSTACK(B5:C7,E5:F7)

Afterward, hit Enter.
That’s it, you have successfully concatenated the multiple arrays in vertical order.

The VSTACK function helps to combine two individual datasets in the cell range B5:C7 and E5:F7 and transfer them into a vertical array.

2.3. Insert HSTACK Function

The last process will guide you to concatenate multiple arrays using the HSTACK function. For this, we will use the same dataset as before but arrange them horizontally like this.

Now, insert this formula in cell C10.

=HSTACK(C4:E5,C7:E8)

Then, hit Enter.
Finally, you will successfully concatenate the arrays and get the output all at once.

The HSTACK function helps to combine two individual datasets in the cell range C4:E5 and C7:E8 and transfer them into a horizontal array.

Things to Remember

You can use the CONCAT function instead of the CONCATENATE function as well.
The process for concatenating arrays is possible for up to 255 text strings.
Make sure, there is no invalid argument in any of the formulas we described above. Otherwise, it will give you #Value! Error.
In the case of the dynamic array, you must insert any of the formulas above in the leftmost cell of your output table. It will then automatically spill the array according to the formula.

Download Practice Workbook

Get this sample file to try the process by yourself.

Concatenate Arrays.xlsm

Conclusion

Finally, we are at the end of our article on how to concatenate arrays in Excel which we discussed with 2 conditions. Let us know if you can suggest any other methods for this.

<< Go Back to Range | Concatenate | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!