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.
Now, let us follow the methods below to concatenate this into a single cell.
1.1. Combine CONCATENATE & TRANSPOSE Functions
- First, select cell C8 and type this formula.
- 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 ")
- Finally, press Enter and you will see the required output.
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.
- Afterward, press Enter.
- Finally, you will successfully concatenate an array like this.
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.
- How to Concatenate Multiple Cells with Comma in Excel (4 Ways)
- Excel INDEX MATCH to Concatenate Multiple Results (With 2 Easy Steps)
- How to Bold Text in Concatenate Formula in Excel (2 Methods)
- Combine Multiple Columns into One Column in Excel
- How to Concatenate Two Columns In Excel (5 Methods)
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
- 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.
- Finally, hit Enter and you will get the multiple arrays all at once.
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.
- Afterward, hit Enter.
- That’s it, you have successfully concatenated the multiple arrays in vertical order.
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.
- Then, hit Enter.
- Finally, you will successfully concatenate the arrays and get the output all at once.
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.
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.
- Concatenate Cells with If Condition in Excel (5 Examples)
- How to Copy CONCATENATE Formula in Excel (5 Easy Ways)
- Excel CONCATENATE Showing Formula Not Result (5 Solutions)
- How to Concatenate Formula and Text in Excel (4 Examples)
- CONCATENATE vs CONCAT in Excel (2 Ideal Examples)
- How to Concatenate Number and Text in Excel (6 Methods)
- Concatenate Names in Excel (5 Easy Ways)