How to Combine Two Columns In Google Sheets (2022)

While working on a spreadsheet, often you have to combine data split across multiple columns.

For example, you may want to combine data of two columns containing the first name and last name of the person respectively.

Manually going cell by cell to type and combine data would take forever to complete (especially when you’re working with a big dataset).

Fortunately, there are a few formulas that make combining data from two or more columns quick and easy.

In this tutorial, I’m going to show you how to combine two columns in Google Sheets using different methods (step-by-step).

Merging and Combining Columns in Google Sheets

There are two common methods to combine columns in Google Sheets: the ampersand Operator and the CONCAT formula.

• The first method (using the “&” ampersand operator along with ARRAYFORMULA) will allow you to not only combine value of two or more columns, but will also allow you to seperate the content with specified character or strings of texts.
• The second method (using the CONCAT function along with ARRAYFORMULA) will allow you to combine values in two column, but you can’t seperate the final value with a character or text string.

In most cases, using the ampersand method is quicker and more convenient than the CONCAT formula. That said, use whichever suits your present need.

01. How to Combine Columns in Google Sheets using Ampersand Symbol?

Suppose you have a dataset as shown below containing the first name and last name of the person in columns A and B respectively.

Now, you want to combine the first name and last name (columns A and B) and get the combined result in column C.

Here’s how you can do this using the Ampersand symbol and ARRAYFORMULA:

1. Click the first cell where you want the combined data to go. In our example, I will be selecting C2

2. Next, type the equal sign = to begin the function, followed by ARRAYFORMULA and an opening bracket (

3. Next, reference the first column you want to combine followed by an ampersand symbol &

NOTE – You can either reference a range of cells like A2:A7 or an entire column like A2:A In our example, I am going to reference the entire column starting from the A2 cell.

4. Enter your seperator in the quoation marks followed by an ampersand symbol &

NOTE: The separator is a symbol or character by which you want to separate your combined text. In our example, I want to separate the first name and last name with a space. So, I will type the space button between quotation marks.

5. Next, reference the second column you want to combine. In our example, I want to combine column B starting from cell B2. So, I will type B2:B

6. Finally, add the closing bracket ) to complete the function and press the Enter key.

Voila! The above steps would instantly combine Column A with Column B.

What if you want to combine more than 2 columns in Google Sheets?

For example, let’s say you have another column (Column C) with the age data and you want to combine all three columns (Column A, B, and C) together.

No Problem! Just add the next separator in double-quotes and reference column C in the above formula.

Your final formula will look something like this:

=ARRAYFORMULA(A2:A&” “&B2:B&”-“&C2:C)

That’s it… Here are the combined results.

02. How to Combine two Columns in Google Sheets using CONCAT Function?

Suppose you have the dataset as shown below and you want to combine the cells from columns A and B.

This can be easily done using the CONCAT function along with ARRAYFORMULA

Here’s how:

1. Select the first cell where you want your array to start! For this example, I’ll be selecting C2

2. Next, input the equal sign = to begin the function, followed by ARRAYFORMULA and an opening parenthesis (

3. Next, type the name of our second function which is CONCAT followed by another opening parenthesis ( for this function.

4. Reference the first column you want to combine followed by a comma ,

NOTE – Just like the ampersand method, you can either reference a range of cells like A2:A8 or an entire column like A2:A In our example, I am going to reference the entire column starting from the A2 cell.

5. Next, reference the second column you want to combine. In our example, I want to combine column B starting from cell B2. So, I will type B2:B

6. Finally, add two closing parentheses )) (one for ARRAY function and other one for CONCAT function) and then press the Enter key.

That’s it… The above steps will instantly combine column A and Column B.

Want to use a separator (like space or dash) between the combined text?

Unfortunately, the CONCAT function does not allow you to use a separator in function.

However, you can use CONCATENATE function instead of the CONCAT function which will give your flexibility to add separators between the combined text.

… but the CONCATENATE function does not work with the ARRAYFORMULA function. That means you won’t be able to combine the entire column with a single formula in one click.

Still, interested in learning how to use CONCATENATE function?

Here you go…

03. How to Merge two Columns in Google Sheets using CONCATENATE Function?

We are going to use the same above example and try to add a dash “-“ as the separator between the category and ID.

1. First of all, select the cell which will contain the combined data. For this example, I’ll be selecting C2

2. Enter equal = sign to begin the function, followed by the name of the function, which is CONCATENATE

3. Next, enter an opening parenthesis ( followed by the first argument you want to combine. In this example, my first argument is going to be A2

4. Enter a comma , first and then enter the seperator in double quote. In this example, I want a dash “-“ between the combined text.

5. Enter a comma again and then add the last argument (with which you want to combine the first argument). In our example, that is going to be B2

6. Finally, enter a closing parenthesis ) and then hit the Enter key.

That’s it… This will merge the first and second cells of both the columns, separated by a dash.

If you want to quickly apply this formula to an entire column (or a big range of cells) use the fill-down feature of Google Sheets.

All you need to do is just select the cell containing the formula >> click and drag the blue square icon appearing in the bottom left corner of the selected cell >> drag this down as far as you’d like to fill the column and then release the left mouse button.

04. Final Thought on Combining Columns:

That’s all folks.

In this guide, I’ve shown you how to combine two columns in Google Sheets using various formulas and functions.

These formulas can really save lots of time that you would otherwise spend on manually combining data.

Over to you…

Which functions (or methods) of combining columns do you find more convenient?

Let me know in the comment section below.