How to Combine Columns in Google Sheets: 4 methods for merging data

When working with spreadsheets, we often need to combine data from multiple columns into a single column. For example, we might need to combine the first and last names of a list of contacts to make the full name or Combine columns for “Street Address,” “City,” “State,” and “ZIP Code” to create a complete address. There are several ways to combine columns in Google Sheets, and the best method for you will depend on the specific data that you are working with.

In this blog post, we will discuss four different methods for combining columns in Google Sheets:

• Using the CONCAT function
• Using the ampersand (&) operator
• Using the CONCATENATE function
• Using the TEXTJOIN function

We will also provide examples of how to use each method so that you can see how they work in practice.

What are the practical scenarios where you might need to combine two columns?

We intentionally create separate fields or columns to maintain a granular data format. This is particularly evident when designing data entry forms, where we structure forms to collect all necessary data. The advantage lies in the ability to later combine them to meet our analysis and presentation needs.

Below are some practical scenarios where you may need to combine columns:

• Full Name: Combining the “First Name” column and the “Last Name” column to create a “Full Name” column.
• Address: Combining columns for “Street Address,” “City,” “State,” and “ZIP Code” to create a complete address.
• Date and Time: Merging “Date” and “Time” columns into a single “Date and Time” column.
• Email Address: Create an “Email Address” column by merging the “Username” and “Domain” columns.
• Phone Numbers: Combining the “Country Code” and “Phone Number” columns to create complete phone numbers.
• URLs: Merging “Domain” and “Path” columns to create URLs for websites.
• Financial Transactions: Combining “Currency” and “Amount” columns to display financial transactions.
• Concatenating Text: Creating custom messages by combining text from different columns.
• CSV Export: Preparing data for CSV exports by combining relevant columns.
• Employee IDs: Creating employee IDs by combining “Department Code” and “Employee Number.”

Combine two columns in Google Sheets using the CONCAT function

The CONCAT function combines two values. This function is a good choice for users who need to combine two text values quickly and easily. It can be considered a simple version of the CONCATENATE function described below.

For example, if you use it as =CONCAT("John", "Doe") it returns the string JohnDoe.

=CONCAT(B1,C1)

To apply the formula to the entire column, drag it down to the last row or modify it using the ARRAYFORMULA like below.

=ARRAYFORMULA(CONCAT(B1:B6,C1:C6))

The disadvantage of the CONCAT function is you can only combine two values. And also you cannot add delimiters (separators) in-between values.

Combine Columns in Google Sheets using the Ampersand (&) Operator

This method is like a shortcut to the CONCATENATE function described below. You can use ampersands (&) without function names to combine the contents of two or more columns into one column.

The basic syntax of this formula is given below.

=value1&value2&value3.....

Unlike the previous method, you can add more than two values in this method. And you can also add delimiters (separators) between values.

=A2&", "&B2&", "&C2

Drag the equation down to apply it to the entire column. You can also modify the formula using ARRAYFORMULA, as shown below, to automatically copy the equation to the entire column.

=ARRAYFORMULA(A2:A4&", "&B2:B4&", "&C2:C4)

A disadvantage of this method is that it can become complex when combining many values.

How to use CONCATENATE function to combine columns

Out of all the methods mentioned here, the CONCATENATE function is the one that is most often used to join cells or values in many spreadsheet programs. In Google Sheets as well, you can utilize this function to merge data from two or more columns into a single column.

The following table gives the basic syntax of the function. In this function, you can use delimiters (operators) between values. So, you can create the combined column by including spaces, commas, hyphens, etc… to separate values from other columns.

In the following formula, I use a comma and a space (“, “) as separators to combine the data in the cells A2, B2, and, C2.

=CONCATENATE(A2,", ",B2,", ",C2)

The disadvantage of this function is that we cannot use it with ARRAYFORMULA to automatically apply the function to the entire column. If you want to use the ARRAYFORMULA, you should use the Ampersand (&) method discussed above.

Combine columns in Google Sheets using the TEXTJOIN function

The TEXTJOIN function in Google Sheets is used to combine multiple text strings into a single string with a specified delimiter between each text item. This function is particularly useful when you want to create a concatenated text string from a range of cells or an array of values.

When using the Ampersand (&) and CONCATENATE methods, you can use distinct delimiters for each location, but with TEXTJOIN, you can only use one delimiter for all locations.

The difference is also that you can leave out empty cells in the TEXTJOIN function. If you use other methods to put together columns with empty cells, they add an extra delimiter where the empty cell is. However, this doesn’t occur with the TEXTJOIN function if you choose to ignore empty cells.

You can combine columns in Google Sheets using the TEXTJOIN function, as demonstrated below.

=TEXTJOIN(", ",TRUE,A2,B2,C2)

The Address column in the table above was created using the TEXTJOIN function. Cell B3 in the table is empty, but the TEXTJOIN function did not include an additional delimiter to represent this empty cell. This is because the TEXTJOIN function can ignore empty cells. This is an advantage over the CONCATENATE function, which does not ignore empty cells.

The TEXTJOIN function also does not work with the ARRAYFORMULA to dynamically fill the entire column.

Comparing Methods for Combining Columns in Google Sheets

Method	Description	Advantages	Disadvantages
CONCAT Function	Combines two values quickly.	Quick and easy. Works with ARRAYFORMULA	Only combines two values. Cannot add delimiters.
Ampersand (&) Operator	Combines multiple values.	Combines two or more text values. Can add delimiters. Add multiple delimiters.	Can become complex with many values.Cannot be used with ARRAYFORMULA.
CONCATENATE Function	Combine multiple values. Commonly used in many spreadsheets.	Combines two or more text values. Can add delimiters. Add multiple delimiters.	Cannot be used with ARRAYFORMULA.
TEXTJOIN Function	Combines text strings with a specified delimiter. Useful for ranges or arrays. Ignores empty cells.	Can ignore empty cells. Combines two or more text values.	Can add only one type of delimiter. Does not work with ARRAYFORMULA

Wrapping Up

In this article, we discussed four different methods for combining columns in Google Sheets:

• CONCAT function: This function combines two values quickly and easily. It is a good choice for users who need to combine two text values quickly and easily. It can be considered a simple version of the CONCATENATE function described below.
• Ampersand (&) operator: This method is like a shortcut to the CONCATENATE function described below. You can use ampersands (&) without function names to combine the contents of two or more columns into one column.
• CONCATENATE function: This function is more flexible than the CONCAT function. It can combine data from two or more columns, and it can use delimiters. However, it cannot be used with ARRAYFORMULA.
• TEXTJOIN function: This function is the most versatile method for combining columns. It can ignore empty cells, and it can use any delimiter you want. However, it does not work with ARRAYFORMULA.

The best method for you will depend on your specific needs and requirements. If you need to combine two values quickly and easily, the CONCAT function is a good choice. If you need to combine multiple values or use delimiters, the ampersand (&) operator or the CONCATENATE function are better options. If you need to ignore empty cells or use a custom delimiter, the TEXTJOIN function is the best choice.