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

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)&” “`

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

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

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

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

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

- 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**

**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
```

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

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

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

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

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

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

**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

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