How to Combine and Concat Cells in Google Sheets (6 Methods)

how to combine and contact cells in google sheets 36

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.

See also  How to Alternate Row Color in Google Sheets [Guide 2022]

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

how to combine and contact cells in google sheets 1

Step 2

Pass the first argument

how to combine and contact cells in google sheets 2

Step 3

Pass the second argument

how to combine and contact cells in google sheets 3

Step 4

Hit Enter and you’re done

how to combine and contact cells in google sheets 4

how to combine and contact cells in google sheets 5

how to combine and contact cells in google sheets 6

how to combine and contact cells in google sheets 7

See also  How to use FORECAST Function in Google Sheets (Best Guide)

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

how to combine and contact cells in google sheets 8

Step 2

Applying the join function on multiple rows and column range

how to combine and contact cells in google sheets 9

Step 3

Join function return an error

how to combine and contact cells in google sheets 10

Step 4

Using text join formula

how to combine and contact cells in google sheets 11

Step 5

Passing the first argument – delimiter

how to combine and contact cells in google sheets 12

Step 6

Passing the second argument – true or false

how to combine and contact cells in google sheets 13

Step 7

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

how to combine and contact cells in google sheets 14

 

Step 8

Hit, Enter and you get the desired result

how to combine and contact cells in google sheets 15

 

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

See also  How to use Color Scale in Google Sheets [Complete Guide]

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

how to combine and contact cells in google sheets 16

Step 2

Write the =CONCAT function

how to combine and contact cells in google sheets 17

Step 3

Pass the first argument (single cell or value)

how to combine and contact cells in google sheets 18

Step 4

Pass the second argument (single cell or value)

how to combine and contact cells in google sheets 19

how to combine and contact cells in google sheets 20

how to combine and contact cells in google sheets 21

how to combine and contact cells in google sheets 22

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.

See also  How to Search in Google Sheets (Complete Guide)

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.

how to combine and contact cells in google sheets 23

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

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

how to combine and contact cells in google sheets 24

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

See also  How to use DIVIDE Function in Google Sheets [User Guide]

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

how to combine and contact cells in google sheets 25

Step 2

In any other cell write the ARRAY FORMULA

how to combine and contact cells in google sheets 26

Step 3

combine ampersand formula inside ARRAY FORMULA
how to combine and contact cells in google sheets 27

Step 4

Hit Enter, and you’re done.

how to combine and contact cells in google sheets 28

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 29

See also  How to use Mail Merge in Google Sheets [User Guide 2022]

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

how to combine and contact cells in google sheets 30

Step 2

Hit enter and you’re done.

how to combine and contact cells in google sheets 31

Step 3

Drag the formula for the entire column

how to combine and contact cells in google sheets 32

how to combine and contact cells in google sheets 33

See also  How to Insert Columns in Google Sheets

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,

how to combine and contact cells in google sheets 34

Step 2

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

how to combine and contact cells in google sheets 35

Step 3

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

how to combine and contact cells in google sheets 36

See also  How to Use REGEXMATCH in Google Sheets [Complete Guide]

Notes

  • Use any of the above methods that best fits your problem statement
  • The concat and concatenate functions are different
  • The ampersand formula has more variations you can make any combination, it should be valid so it will work
  • Array formula can also be combined with any formula to work on multiple rows and columns

See also  How to Connect Google Forms to Google Sheets (With Examples)

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!!

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general