Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

Get this sample file to try the process by yourself.


2 Conditions to Concatenate Arrays in Excel

The procedure 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.

Concatenate Single Array in Excel


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

1.1. Combine CONCATENATE & TRANSPOSE Functions

We can easily combine the 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)&” “

Concatenate Single Array in Excel

  • 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 ")

Excel Concatenate Arrays

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.

Read More: Combine CONCATENATE & TRANSPOSE Functions in Excel


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.

Concatenate Single Array in Excel

  • 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.

Read More: How to Combine Text from Two or More Cells into One Cell in Excel (5 Methods)


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)

Concatenate Single Array in Excel

  • 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.

Read More: How to Concatenate String and Variable in Excel VBA (A Detailed Analysis)


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.

Concatenate Single Array in Excel

  • 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.

Read More: How to Concatenate Multiple Cells With Space in Excel (7 Methods)


Similar Readings


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.

Concatenate Single Array in Excel

  • 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.

Read More: VBA to Concatenate Range with Separator in Excel (3 Ways)


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.

Concatenate Multiple Arrays with Excel Formulas

  • 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.

Read More: Concatenate Numbers in Excel (4 Quick Formulas)


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.

Concatenate Multiple Arrays with Excel Formulas

  • 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.

Read More: How to Concatenate Multiple Cells in Excel (7 Easy Ways)


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.

Concatenate Multiple Arrays with Excel Formulas

  • 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.

Read More: How to Concatenate String and Integer using VBA


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.

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. Follow ExcelDemy for more Excel related tutorials.


Related Articles

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo