During data entry on Sheets, there can be several instances when you would want to combine multiple cells into one. In most cases, you may have to join text strings of separate columns. Maybe combine Streets and City’s name to return the full address.
You might want to actually merge the cells to highlight data headings. Or, simply combine duplicate cells to have a unique value in your sheet. In this article, we’ve covered methods for all these three cases in detail.
Case 1: Merge Cells
If you need to emphasize a heading in Sheets, you can Merge cells. Although you could’ve used Wrap Text for this, it changes the Column size too. So, here, we will combine multiple cells into one so that the rest of the Column and Row sizes are intact.
Before you begin, check out what each Merge cells options look like.
To merge all cells, select cell ranges you think you’d need for a topic. Then, from the Sheets toolbar, click on the Merge cells icon. Now, you can enter the topic and adjust the font.
Alternatively, you can also head to Format tab > Merge cells. Then, choose an option of how you want to merge cells. Here, we picked Merge all.
Case 2: Combine Different Cells Into One
Another case when you may find the need to combine cells is to merge two columns/rows together. For this, we will use several Sheet functions.
Using Ampersand Operator
Firstly, you can use Ampersand (&) Operator to combine cell values in Sheets. It is beginner friendly as you do not have to use any functions. Use this method if you want to combine a small data set in Sheets.
Example 1: Combine Texts With No Space
Say you need to join the name and email address in this example. To do so, we have entered the formula as
=A20&B20
Example 2: Combine Texts Without Delimiter
Suppose, you need to combine the first name and last name as in the given data. For this, you need a space between the names. So, we will enter the formula as
=A14 & " " & B14
Here the Ampersand Symbol combines cell contents from A14 and B14. “ ” will return the space. You can drag down the Smart Fill handle to copy the formula for other cells too.
Example 3: Combine Texts With Delimiter
There can be instances when you need to insert a delimiter while merging the texts. In the given case, let’s combine the Street and State with a comma delimiter in between them.
In a new cell, type in the formula as
=A2 & " , " &B2
Using CONCATENATE Function
Instead of entering the Ampersand Symbol to join cells, you could also use the function. In Sheets, the CONCATENATE function is just the formula edition of the Ampersand Operator. This method is very convenient when you need to combine multiple text strings.
The CONCATENATE function just takes a number of strings as an argument.
Syntax: CONCATENATE(string 1, [string2,....])In the given case, let’s combine all Streets, States, ZIP Codes, and Countries to form a full Address. To do so, we’ve entered the formula as
=CONCATENATE(A2:D2)
Alternatively, if you want to add a delimiter between these addresses, the formula would be
=CONCATENATE(A2, " ", B2, ",",C2, ",",D2)
The formula takes A2 as string 1, “ ” as string 2, B2 as string 3, and so on. Notice how we’ve left the first inverted comma (“ ”) empty in the formula to return space between the A2 and B2. Similarly, we’ve added a comma (“,”) thereafter to return a comma between B2, C2, and D2.
Using TEXTJOIN
Although CONCATENATE function is extremely useful for merging multiple cells, I personally do not use it if I’ve to add a delimiter. The formula gets lengthy which can be more prone to errors. So, to combine cells while adding a delimiter, I use the Sheet’s TEXTJOIN function.
TEXTJOIN function returns the combined strings with a delimiter and is also easy to use.
Syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2, …]Again, let’s try joining all Streets, States, ZIP Codes, and Countries to form a full Address using the TEXTJOIN function. In a new cell, we’ve typed in the formula as
=TEXTJOIN(",", TRUE, A2:D2)
Let’s check out the formula arguments below.
- “,” is a specified delimiter to add in between strings.
- TRUE ignores blank cells.
- A2:D2 are texts.
Case 3: Combine Duplicate Cells
Some of you may wish to Combine cells to eliminate duplicate strings in Sheets. To do so, Sheets has a default Remove duplicates menu in the data tab.
- Select duplicate Cell ranges.
- Head to Data Tab.
- Click on Data clean-up > Remove duplicates.
- Again, click Remove duplicates.
