How to Combine Two Columns in Google Sheets

How to Combine Two Columns in Google Sheets

There are often situations when you need to combine data. There can be various reasons why you need to combine data from two columns into one. It can be a requirement for a formula, you just simply want the combination data in two columns. Learning to do it is as simple as following a few steps. It is important to know these formula tips and tricks so that you don’t end up wasting your time as and when required. Continue reading this article as it going to be a perfect guide for you to get started with learning about how to combine two columns in Google Sheets and also about methods to combine cells in Google Sheets.

How to Combine Two Columns in Google Sheets

How to Combine Two Columns in Google Sheets

Keep reading further to find the steps explaining how to combine two columns in Google Sheets in detail with useful illustrations for better understanding.

What are the Formulas to Combine Two Columns in Google Sheets?

Listed below are the formulas that can be used to combine two columns in a Google Sheets vertically as well as horizontally.

A. Formulas to Combine Horizontal Columns

  • =ARRAYFORMULA(A2:A11&”-“&B2:B11)
  • =ARRAYFORMULA(CONCAT(A2:A11,B2:B11))

B. Formulas to Combine Vertical Columns

  • =FILTER({A2:A11;B2:B11},LEN({A2:A11;B2:B11}))
  • ={A2:11;B2:B11}
  • =UNIQUE({A2:11;B2:B11}

Further in the article, you will learn how to combine two columns in Google Sheets using these formulas and also some methods that will teach you to combine cells in Google Sheets.

Also Read: Difference Between Rows and Columns in Excel

How to Combine Two Columns in Google Sheets?

Let’s begin with the methods to combine two columns vertically on Google Sheets:

Method 1: Use ARRAY FORMULA

This method can be used when you have data in two columns that are supposed to be combined with a space, comma (,), or a hyphen (-) in between. For instance, have a look at the data sample given below.

Sample Data

Here, we have a list of departments and staff of the respective departments. We will be using the ARRAY formula to combine both data columns using a hyphen (-) in between. Below mentioned are the steps to get started with the process:

1. Start to apply the formula by typing equal to (=)ARRAY in cell C2 as that is where we want the target value.

Start to apply the formula by typing equal to ARRAY in cell C2 as that is where we want the target value

2. Select the Department column and add an ampersand (&) and a separator (-) to select the next column.

Select the Department column and add an ampersand (&) and a separator so that you can select the next column

3. Add an ampersand (&) and select the Staffs column.

Add an ampersand and select the Staffs column. How to Combine Two Columns in Google Sheets

4. Close the bracket to complete the ARRAY Formula.

Close the bracket to complete the ARRAY Formula. How to Combine Two Columns in Google Sheets

5. Press the Enter key to get the result.

Press Enter to get the result

Note: Here we have used a hyphen (-) as a separator you can use a comma (,), space ( ), or vertical bar (|) accordingly.

This is how you can use the ARRAY FORMULA to combine two columns into one. Further in this article, you will also read about how to combine multiple cells into one column

Also Read: How to Add Multiple Lines in One Cell in Google Sheets

Method 2: Use ARRAY FORMULA with CONCAT Function

ARRAY FORMULA with CONCAT functions is one of the other ways in which you can use to combine two columns. The only limitation of this method is that you cannot combine the columns with a separator. However, this method will be useful when the combination of cells does not require a separator. In this case, we will be using the below-mentioned data that contains Department Code and ID No.

Sample Data. How to Combine Two Columns in Google Sheets

Let’s proceed with the steps of this method:

1. Start by applying the ARRAY FORMULA by typing equal to (=) ARRAY in cell C2 where we want the target value.

Start by applying the ARRAY FORMULA in cell C2 where we want the target value

2. Now, type CONCAT to get started with the CONCAT function.

Now type CONCAT to get started with the CONCAT function. How to Combine Two Columns in Google Sheets

3. To enter value1, select the Department Code column and add a comma (,) to move to the next value.

To enter value1 select the Department Code column and add a comma to move to the next value

4. For value2, select ID No. column and close the bracket to complete the formula.

For value2 select ID No. column and close the bracket to complete the formula

5. Press Enter key to get the target value.

Press Enter key to get the target value. How to Combine Two Columns in Google Sheets

This is how to combine two columns in Google Sheets using the ARRAY FORMULA with the CONCAT function. Let’s move on to the next method to merge cells in Google Sheets.

Method 3: Use FILTER with LEN Function

The FILTER with the LEN function is used in situations when you need to combine one column with another vertically. One advantage of using this method is it eliminates the blank cells within the range. One such example is what we will be looking at here. The below-given data consists of employee names from Shift 1 and Shift 2. By using the FILTER with the LEN function, we will see how to combine multiple cells into one column.

Sample Data

Listed below are the steps to use FILTER with the LEN function:

1. Start by entering the =FILTER function in Cell C2 where need the target value.

Start by entering the FILTER function in Cell C2 where need the target value

2. Open curly brackets and select the Shift 1 column and add a semi-colon (;).

Open curly brackets and select the Shift 1 column and add a semi-colon

3. Select the next column which is the Shift 2 column and close the curly bracket.

Select the next column which is the Shift 2 column and close the curly bracket. How to Combine Two Columns in Google Sheets

4. Add a comma (,) and get started with the LEN function by typing LEN.

Add a comma and get started with the LEN function by typing LEN

5. Open a curly bracket and select shift 1 column again.

Open a curly bracket and select shift 1 column again

6. Add a semi-colon (;) and select the shift 2 column again.

Add a semi-colon and select the shift 2 column again

7. Close the curly bracket and the bracket of the LEN formula to complete it.

Close the curly bracket and the bracket of the LEN formula to complete it

8. Close the bracket for the Filter function.

Close the bracket for the Filter function

9. Press Enter key to see the target values in place.

Press Enter key to see the target values in place

This is how you can use the FILTER function with the LEN function to vertically combine cells in Google Sheets. Now we have a look at the next method to see how to combine multiple cells into one column.

Also Read: How to Freeze Rows and Columns in Excel

Method 4: Use Curly Brackets and Semi-colon

Using curly brackets and semi-colon is one of the easiest methods for vertically merging or combing columns into one. This method turns out useful when you do not want to make any changes to the source data. Using curly brackets and semi-colon enables the users to stack up the data as it is. To explain this method the data we will be using is the same one that we used for the above-mentioned method. We just have made some new duplicate entries to see how the formula unfolds. This is the sample data you can see a few duplicate entries.

Sample Data

Follow the below-mentioned steps to see how this method can be used to merge cells in Google Sheets:

1. Start with an equal to (=) and then open curly brackets in cell C2 where you want the target value.

Start with an equal to and then open curly brackets in cell C2 where you want the target value

2. Select the first column which is the Shift 1 column in the data.

Select the first column which is the Shift 1 column in the data

3. Add a semi-colon (;) and select the second column which is Shift 2.

Add a semi-colon and select the second column which is Shift 2. How to Combine Two Columns in Google Sheets

4. Close the curly bracket.

Close the curly bracket

5. Press Enter key to get the target value.

Press Enter key to get the target value

This is how you use this method to combine cells in Google Sheets. Though it stacks the data as required it also contains blank cells and duplicate details. If this is not a method that will work for you, you can try the next method.

Method 5: Use UNIQUE Function

The UNIQUE function is one of the methods that can help you combine columns vertically. This method is extremely useful when you want to remove duplicate and blank cells, unlike the above-mentioned method that stacks all the data as given. Therefore, we will be using the same data sample as used above. Let’s begin with the steps that can be used to perform this method.

1. Start by putting an equal to (=) and type the UNIQUE formula in cell C2 which is where we want the target value.

Start by putting an equal to and type the UNIQUE formula in cell C2 which is where we want the target value

2. Open a curly bracket and select the first column Shift 1.

Open a curly bracket and select the first column Shift 1. How to Combine Two Columns in Google Sheets

3. Add a semi-colon (;) and select the second column Shift 2.

Add a semi-colon and select the second column Shift 2

4. Complete the formula by adding a curly bracket and a bracket.

Complete the formula by adding a curly bracket and a bracket

5. Press the Enter key to get the target value.

Press the Enter key to get the target value

As you can see, there is just one entry with one name. This method removes the duplicates and blank cells. There is only one blank cell in the target value that separates the values of different columns. Moving on we will be having a look at the how CONCATENATE function can be used to combine data from two columns into one.

Method 6: Use CONCATENATE Function

Using CONCATENATE function is another method that can be used as an alternative to the above-mentioned methods. This method slightly functions in a different manner than the rest. As in the rest of the methods we selected the whole column that we wanted to combine whereas with this function you will combine the cells and copy and paste the formula throughout the cell to combine the columns. Another point to be noted while using this method is that you will have to add an additional column if you require a separator. This separator can be a space ( ), comma (,), hyphen (-), vertical bar (|), etc. The data sample that we are going to use contains the Employee name, Separator, and Incentive. We will be combining these three columns.

Sample Data

Let’s proceed with the steps to see how this method functions:

1. Put an equal to (=) and start by typing CONCATENATE in cell D2 where we want the target value.

Put an equal to and start by typing CONCATENATE in cell D2 where we want the target value

2. Select the first cell that has to be combined. Here, it is A2.

Select the first cell that has to be combined Here it is A2

3. Add a comma (,) and select the separator cell if you want to add any.

Add a comma and select the separator cell if you want to add any. How to Combine Two Columns in Google Sheets

4. Add comma (,) and select the third cell that needs to be combined which is cell C2 in this case.

Add comma and select the third cell that needs to be combined which is cell C2 in this case

5. Close the bracket to complete the formula.

Close the bracket to complete the formula

6. Press Enter key to see the target value.

Press Enter key to see the target value. How to Combine Two Columns in Google Sheets

7. Drag the corner of the D2 cell to copy the formula throughout the column.

Drag the corner of the D2 cell to copy the formula throughout the column

Note: If you do not want the separator column in between after combining the value, you can hide the column. If you want to delete the separator column you can cut and paste the Combined Value column. While pasting select the paste value only option. If you do not follow this option you will lose the combined value in case you delete the separator column

These were the methods to combine two or more columns into one vertically and horizontally using the above-mentioned methods.

Also Read: 3 Ways to Combine Multiple PowerPoint Presentation Files

Method 7: Use Ampersand with Separator

Using an ampersand with a separator is one of the easiest ways to merge cells in Google Sheets and then combine columns if needed. To explain this method, we will be using the set of data that contains the Employee’s Name and Surname. We will use this method to get the full name in the third column.

Sample Data

Mentioned below are the steps to get started with this method:

1. Put an equal to (=) in the C2 cell where you want the target value.

Put an equal to in the C2 cell where you want the target value

2. Select the first cell that has to be combined which is A2, add an ampersand (&), and in quotations add space ( ) as a separator.

Select the first cell that has to be combined which is A2 add an ampersand and in quotations add space as a separator

3. Put an ampersand (&) again, and select the second cell that has to be combined which is B2.

Put an ampersand again and select the second cell that has to be combined which is B2

4. Press Enter key to get the full name.

Press enter key to get the full name. How to Combine Two Columns in Google Sheets

5. Apply the formula through the column by dragging the corner of cell C2.

Apply the formula through the column by dragging the corner of cell C2

Note: Instead of using space ( ) as a separator you can also use other separators accordingly as mentioned above in the article.

Now we will move to the next method that you might also find helpful for combing columns in Google Sheets.

Method 8: Use JOIN Function

Using JOIN functions is also as easy as using the above-mentioned methods. Though the syntax of this formula may sound new to you as it contains terms like delimiter which is nothing but the separator. To explain this method we will be using the same data that we used in the above-mentioned method. Let’s proceed with the steps straight away.

1. Put an equal to (=) and start typing the JOIN formula in the cell where you want the target value.

Put an equal to (=) and start typing the JOIN formula in the cell where you want the target value

2. For the delimiter argument, add space ( ) in quotations and put a comma (,) to move to the next argument.

For the delimiter argument add space in quotations and put a comma to move to the next argument

3. Select the first cell A2 that needs to be combined and add a comma (,).

Select the first cell A2 that needs to be combined and add a comma. How to Combine Two Columns in Google Sheets

4. Select the second cell B2 and close the bracket to complete the formula.

Select the second cell B2 and close the bracket to complete the formula

5. Press the Enter key to get the target value.

Press the Enter key to get the target value

6. Apply the formula through the column by dragging the corner of cell C2.

Apply the formula through the column by dragging the corner of the cell C2

Recommended:

This article was all about the methods that you can use to combine multiple cells and columns into one. Each method caters to different data types and can be used accordingly. We hope that this guide was helpful and you were able to learn how to combine two columns in Google Sheets. Let us know which method worked for you best. If you have any queries or suggestions, then feel free to drop them in the comments section below

Posted by

Pete Mitchell

Pete is a Senior staff writer at TechCult. Pete loves all things technology and is also an avid DIYer at heart. He has a decade of experience writing how-tos, features, and technology guides on the internet.

Leave a Reply

Your email address will not be published. Required fields are marked *