How to Combine and Concat Cells in Google Sheets [6 Methods]

Combining and concating data in google sheets is always a need of us in many cases, in this article, we will learn how to combine and concat cells in google sheets, keep in mind this is the data concatenation and combination, it’s not cell merging.

We will see cell merging in another article. So how do combine and concat cells in google sheets, let’s say I have something written in a cell, and in the next cell I have some related information, now in another cell I want to have the combination of both within one cell. How do I do this? it can be simply done manually, but what if want to do the same for 1000 cells? We have different methods and techniques to combine and concat cells in google sheets, we will see various methods and will analyze the best method to use in a particular situation. Let’s move on to some theoretical concepts about combine and concat in google sheets.

Use cases of using combine and concat cells in google sheets

When working with a huge amount of data, we have different columns that are common in most data sets, when we have a column containing a physical address of a person of a company, is written very long Street 1844 E Fairway Dr – City Orange – State California. So, we have street, city, and state in different columns generally. But what if we need to extract the full address in a single column, again we do it manually, but what if we have 1000 entries? Of course, we need some method, some formula, some trick to resolve this. We will see many methods, formulas, and techniques to do it. Let’s move on to the step-by-step procedure to learn it.

How to combine and contact cells in google sheets

Here, we are going to use sample use cases and general scenarios to understand this problem and will use various methods to resolve it. Now, it’s up to you and your problem which method suits your problem and you can use any method.

So first we’re going to see the problem, and what the problem statement looks like.

I have an example of class and section columns and I need a full class name to search in the database so I have to join the two respective columns for all the data.

To solve this problem, we have various methods we will see all of them in different sections so let’s move on to the first section.

How to combine and contact cells in google sheets using – the JOIN function

In this first section, we will see how to combine and concat cells in google sheets using the join function. Now, join is a built-in function that does nothing but join two or more cells’ data together in another cell. In our example, we will use this method and see how it works.

Step 1

In the adjacent column write the =join function

Step 2

Pass the first argument

Step 3

Pass the second argument

Step 4

Hit Enter and you’re done

How to combine and contact cells in google sheets – using the TEXTJOIN function

In this first section, we will see how to combine and concat cells in google sheets using the text-join function. The join function and text join function have some differences. The join method can only work on a single row or column, we can’t select a range like A2:C3, for working on multiple rows and columns we have another function that is text join, so now we are going to see the text join method with another example to see how it works.

Step 1

Sample data

Step 2

Applying the join function on multiple rows and column range

Step 3

Join function return an error

Step 4

Using text join formula

Step 5

Passing the first argument – delimiter

Step 6

Passing the second argument – true or false

Step 7

Passing the third argument – the multi-row-column range

Step 8

Hit, Enter and you get the desired result

This is the main difference between the join and text join function, join only takes a single row or column as input whereas, the text join can take multiple rows or columns along with a Boolean argument that tells the function to count or skip the empty cells

How to combine and contact cells in google sheets – using the CONCAT function

In this first section, we will see how to combine and concat cells in google sheets using the concat function. Now, this is a very straightforward function but you should learn it to use in simple use cases, you don’t need to use long functions in simple concat cases. So here is the concat function that can only join two cells, not even two ranges. It takes two cells as input and returns the concat value.

Step 1

Sample data

Step 2

Write the =CONCAT function

Step 3

Pass the first argument (single cell or value)

Step 4

Pass the second argument (single cell or value)

Step 5

Hit Enter and you’re done

This is a very simple function to concat two values or cells, it does not allow to use of any delimiter but when using values, you can use a single spacebar before the second value to create a gap between two values, but when using cell addresses you cannot use any space in between.

This function is not recommended. Although I added it to the list to let you know how it works.

How to combine and contact cells in google sheets using the AMPERSAND “&” Formula

In this first section, we will see how to combine and concat cells in google sheets using the ampersand & method. This method is very useful and can be used with different variations based on your requirements. Let’s start with a simple ampersand & method formula.

The simple formula for combining two cells is A2&B2.

The above formula will concat the two cell values similarly as we did in the previous section using the concat function, but this formula can join more than two values that the concat function can’t. Concat can only join two cells or values.

Although we have another similar function ”CONCATENATE” in google sheets, that can concat values like the concat function but can also add more than two values or cells.

Here, let’s see the implementation of the ampersand “&” formula.

Adding any separator with value using ampersand “&” Formula

Another variation of the formula is =A1&B1 is =A2&”-“&B2, using this we can add any separator with the values. Let’s implement this formula on the same data.

The formula will be extended up to any range, here I extend it up to 3 rows.

=A1&”-“&B1&”-“&C1

That’s how we can use the ampersand formula to extend the number of rows or columns following the same syntax.

Using ArrayFormula with ampersand “&”

Here is another variation of the ampersand formula, here we will use it inside an ARRAY FORMULA to concat two adjacent columns into one.

For this section, we will see another example of addresses where we will combine the parts of the addresses to make a full address in a column.

Step 1

Sample data in two adjacent columns

Step 2

In any other cell write the ARRAY FORMULA

Step 3

combine ampersand formula inside ARRAY FORMULA

Step 4

Hit Enter, and you’re done.

This method does not allow you to add any separator, it uses a space separator by default, if you want any other separator of your choice, you can use the array formula along with the previous ampersand formula. It will become

=ARRAYFORMULA(A2:A6&”-“&B2:B6&”-“&C2:C6)

How to combine and contact cells in google sheets – separated by the line break

In this section, we will how to combine and concat cells in google sheets separated by the line break, the line break will be our separator, so each column of data will be going to another line within a single cell. For this method we will use a custom formula, we will use the same example of addresses as we used in the previous section. We will append all the addresses attribute into one column but every part of the address is in a separate line.

The formula is

=A2&char(10)&B2&char(10)&C2

Step 1

Use this formula in any cell

Step 2

Hit enter and you’re done.

Step 3

Drag the formula for the entire column

Notes:

A2&char(10) means the value from the A2 cell and then a line break; here the char(10) means a line break.

How to combine and contact cells in google sheets – multiple columns into one column

In this section, we will see how to combine and concat cells in google sheets – multiple columns into one column, the entire data into multiple columns will be into one single column. Let’s see how it works.

The custom formula for this method is

=FILTER({A2:A7;B2:B8;C2:C8}, LEN({A2:A7;B2:B8;C2:C8}))

Step 1

Write the =Filter() function and pass all data range,

Step 2

add a comma, write the =Len() function and pass the same data range

Step 3

Finish the formula and hit enter, you’re done.

Notes

Frequently Asked Questions

Can I Use the Same Methods to Combine First and Last Names in Google Sheets?

Conclusion

So today, we learned how to combine and concat cells in google sheets, we have learned the various method and tried to implement them for solving different problems. I hope you guys like this article and have learned a lot from this. Don’t forget to share, like, and subscribe to our blog. Thank you!!