In business and finance, combining data from multiple columns can be crucial for analysis and decision-making. One way to do this is through the process of concatenating two columns in Google Sheets. For example, by concatenating a column of first names with a column of last names, businesses can create a single column of full names for more efficient customer data management. There are various ways to learn how to concatenate two columns in Google Sheets, and this article aims to provide a comprehensive understanding of the process.

## A Sample of Practice Spreadsheet

You can copy our spreadsheet that we’ve used to prepare this article.

## 2 Ways to Concatenate Two Columns in Google Sheets

Concatenating two columns in Google Sheets is a quick and easy way to combine data from different columns into one. There are two main ways to concatenate columns in Google Sheets: horizontally and vertically.

### 1. Concatenate Two Columns Horizontally

Concatenating two columns horizontally in **Google Sheets allows you to merge** the data from two separate columns into one, with the data presented horizontally for each row. This is useful for organizing and formatting your data in a more efficient way.

#### I. Using ARRAYFORMULA with Ampersand (&)

**The ARRAYFORMULA function** in Google Sheets is a powerful tool that allows users to apply a single formula to a range of cells with just one input. One way to utilize this function is by combining it with the **Ampersand (&)** operator.

We will use the dataset above to demonstrate the use of **ARRAYFORMULA** with **Ampersand (&)** to concatenate two columns.

**Steps:**

- Firstly, we will select cell
**C13**.

- Secondly, we will use the following formula to combine the data from two columns.

`=ARRAYFORMULA({C5:C9&", "&F5:F9})`

- In this formula,
**ARRAYFORMULA**is being used to apply the**Ampersand**function to the range of cells**C5:C9**and**F5:F9**. - This formula will combine the text in the cells in columns
**C**and**F**, separated by a**comma**and a**space**. - Finally, press
**ENTER**to see the result.

The resulting formula combines the text in the specified ranges with a **comma** and **space** in between, creating a concatenated string for each row in the range. The **FORMULA****ARRAY **function in combination with the **Ampersand (&)** symbol is a useful tool for efficiently **concatenating multiple cells** in a column in Google Sheets.

#### II. Utilizing CONCAT Function

We can combine two **strings into a single cell in Google Sheets** by using **the CONCAT function**. Assume that we will use the previous dataset to explain the use of the **CONCAT** function.

**Steps:**

- Once again, we will select cell
**C13**. - We will use the following formula.

`=CONCAT(C5,F5)`

- Combining the text of two or more cells into one cell is one technique to use
**CONCAT**.

- We shall discover that the
**CONCAT**function will combine the values from cells**C5**and**F5**. As a result, we will discover that Google’s revenue is**$160B$34.3B**. - Using the
**Fill handle**feature of google sheets we can get the result of the full**Revenue Information**column.

**NOTE:**By default, the

**CONCAT**function can only combine two values.

#### III. Utilizing ARRAYFORMULA with CONCAT

We can use the functions **ARRAYFORMULA** and **CONCAT** in order to join two columns automatically, without the use of a **fill handle**. We will use the previous dataset as an example to show the use of these functions.

**Steps:**

- At first, we are once again going to select cell
**C13**. - We will use the following formula.

`=ARRAYFORMULA(CONCAT(C5:C9,F5:F9))`

- To conclude, press
**ENTER**.

**Formula Breakdown:**

- The
**CONCAT**function returns the concatenation of two ranges from**C5:C9**and**F5:F9**. **ARRAYFORMULA**will then display the value returned by the**CONCAT**Function in a single array of rows.

Thus, the **ARRAYFORMULA** and **CONCAT** functions allow us to combine data from two separate columns into a single column for improved data display.

**NOTE:**Press and hold

**CTRL, SHIFT**, and

**ENTER**keys together on the keyboard for

**ARRAYFORMULA**Shortcut.

#### IV. Applying CONCATENATE Function

We can use **the CONCATENATE function** to combine multiple cells or strings into one cell. We will provide the **cell references or strings** to be combined as arguments to the function. The following dataset will make a good example to demonstrate the use of the **CONCATENATE** function.

We will make a single column combining the **Year 1** and **Year 2 Revenue** of the **companies** and rename it into **Revenue Information** using the following formula.

**Steps:**

- Let’s select cell
**C13**first. - Then we will use the following formula.

`=CONCATENATE(C5,", ",F5)`

- Now we will use the
**fill handle**feature of google sheets to get results for the rest of the companies.

**Formula Breakdown:**

- The
**CONCATENATE**function starts by taking the text in cell**C5**. - Next, it adds a
**comma**and then**adds a space**, represented by the**“, “**in the formula. - Finally, it adds the text in cell
**F5**.

This function helps simplify and combine data, making it easy to view and understand the **revenue** for **two years** at a glance.

**Read More: ****How to Concatenate Strings with Separator in Google Sheets**

**Similar Readings**

**How to Append Text in Google Sheets (An Easy Guide)****Add Space with CONCATENATE in Google Sheets****How to Concatenate Text and Formula in Google Sheets (7 Ways)**

### 2. Concatenate Two Columns Vertically

Concatenating two columns vertically in Google Sheets can be done using the following methods. This is useful for creating a comprehensive list of items or data points, without the need to manually copy and paste

#### I. Using Simple Array Reference

Simple array references are a way to reference a group of cells in a worksheet in a single formula. They are created by enclosing a range of cells in **curly braces {}**. This allows you to quickly reference a range of cells, rather than having to enter each individual cell reference into a formula. This can save time and help make your formulas more organized and easy to read.

We will be using the above dataset to demonstrate the process.

**Steps:**

- First, we need to select the cell, for this case, it is
**B13**. - Later on, we will use the following formula.

`={B5:B9;E5:E9}`

- After pressing
**ENTER**, We will find that it displays the result of the array reference.

- Additionally, We can use this same formula to fill up the
**Revenue**column in by selecting cell**C13**.

`={C5:C9;F5:F9}`

- This formula will create a simple array reference by combining the values of the cells in the range
**C5**to**C9**and**F5**to**F9**, and displaying them in a single array.

By using the formula, we can now view the total** revenue** of each** company** in one table instead of two duplicate tables.

**Read More: How to Concatenate If Cell Is Not Blank in Google Sheets (7 Ways)**

#### II. Implementing UNIQUE Function

**The UNIQUE function** is important for identifying and removing duplicate values in a dataset. It is commonly used to ensure data accuracy and integrity. However, it can also be used to extract distinct values from a combined range of cells.

We will be using the above-mentioned dataset to illustrate the use of the **UNIQUE** function to concatenate two columns in google sheets.

**Steps:**

- Once again, we will select cell
**B13**first. - Now we will use the following formula.

`=UNIQUE({B5:B9;E5:E9})`

- This formula uses the
**UNIQUE**function to find unique values in two defined ranges of cells**C5:C9**and**F5:F9**separated by a**semicolon (;)**using**curly braces {}**to group cells and use it as a reference. The result will be a list of unique values.

- We will use the following formula in cell
**C13**to complete the table.

`=UNIQUE({C5:C9;F5:F9})`

In conclusion, by combining two tables into one and reducing the number of columns, we now have a more organized and efficient way of displaying the **companies** and their **revenues**.

**Read More: How to Concatenate Values for IF Condition in Google Sheets**

#### III. Combining FILTER and LEN Functions

Efficient data management requires the use of practical tools.** FILTER** and **LEN functions** in Google Sheets allow for quick data extraction and validation. Use them to organize and analyze your data effectively.

By utilizing the **FILTER** and** LEN functions**, we can combine two tables into one using the same dataset.

**Steps:**

- Once Again, select cell,
**B13**. - Now, we will use the following formula to list the
**company**names.

`=FILTER({B5:B9;E5:E9},LEN({B5:B9;E5:E9}))`

**Formula Breakdown:**

- Inside the
**FILTER**function, the range of cells being filtered is**{B5:B9;E5:E9}**. This range includes cells**B5**through**B9**and**E5**through**E9**. - The condition used to filter the cells is
**LEN({B5:B9;E5:E9})**.**LEN**function is used to determine the number of characters in a cell or text string. - By using the
**LEN**function inside the**FILTER**function, we’re able to filter out cells with a certain number of characters, in this case, it will filter out cells that have no characters or empty cells. - The final result is a filtered range of cells that contains only cells with character.

- Next, we will create a list by combining the
**Revenue**columns. - We will select cell
**C13**and apply the following formula.

`=FILTER({C5:C9;F5:F9},LEN({C5:C9;F5:F9}))`

By using the same process and condition, the formula extracts all the **revenues** of the **companies**. Therefore, we can use the **FILTER** and **LEN** functions to concatenate two columns in Google Sheets.

#### IV. Concatenating Large Dataset with QUERY Function

In Google Sheets, we may concatenate enormous amounts of data over several columns by using **the QUERY function**. To illustrate how to use this function, we’ll use the subsequent dataset.

**Steps:**

- The first step is to choose cell
**B13**. - Next, we’ll combine
**the TRANSPOSE function**with the**QUERY**function.

`=TRANSPOSE(QUERY(TRANSPOSE(B5:D9),,9^9))`

- Finally, press
**ENTER**to see the result.

**Formula Breakdown:**

- The inner
**TRANSPOSE**function swapped the rows and columns of range**B5**to**D9**. **QUERY**function uses the values returned by the**TRANSPOSE**function as data.- By choosing
**9^9**, the header of the data rows for the**QUERY**function is equivalent to infinite. - The outer
**TRANSPOSE**function once more reverses the values that the**QUERY**function returned.

We can see that the formula has concatenated all three adjacent columns.

If the data set is not adjacent to each other, we can use the following formula to concatenate **multiple columns.**

`=TRANSPOSE(QUERY(TRANSPOSE({B5:C9,F5:F9}),,9^9))`

**Read More: Google Sheets QUERY Function to Concatenate Two Columns**

## Conclusion

By using these functions, data management becomes more efficient as the number of columns is reduced and information is displayed in a more streamlined manner. This approach allows for easy organization and extraction of specific data, leading to better decision-making and improved business performance.

In summary, understanding how to concatenate two columns using these functions can improve organizational efficiency and support economic growth. Check **OfficeWheel** for more help.