M. Shaiq Ansari
To Combine and Concat Cells in Google Sheets
Using TEXTJOIN Function:
- Use =TEXTJOIN(delimiter, ignore_empty, range) to combine values from multiple rows and columns.
- This method is versatile and allows you to specify a delimiter while ignoring empty cells, making it suitable for a wide range of concatenation needs.
OR
Using Ampersand (&) Formula:
- The & formula, such as =A1&B1, is simple and effective for combining two cell values.
- It offers flexibility to create variations, like adding separators or combining multiple values.
OR
Using FILTER Function:
- The FILTER function allows you to merge data from multiple columns into a single column.
- It’s especially useful when you need to concatenate data from multiple sources and create a consolidated column.
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
- 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
Frequently Asked Questions
Can I Use the Same Methods to Combine First and Last Names in Google Sheets?
When working with Google Sheets, merging first and last names can be easily accomplished using the same methods. Whether you need to combine names for a mailing list or data analysis, the process remains consistent. Utilize formulas like CONCATENATE or the ampersand symbol (&) to swiftly merge first and last names without any hassle.
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!!
