ExcelInsider Team
Concatenating multiple cells in Google Sheets is essential for combining data from different cells into a single cell. It enables users to better organise data and streamline data presentation. Google Sheets has many built-in tools that you can use to concatenate multiple cells.
To merge data from multiple cells in Google Sheets, follow the steps below:
➤ Open the spreadsheet file.
➤ Select the cell where you want to display concatenated values and put the formula:
=CONCATENATE(A1,B1,C1)
➤Replace A1, B1 and C1 with the references of the cells that you want combined.
➤ Hit Enter, and you should have your cells concatenated.
Concatenate Multiple Cells Using the CONCATENATE Function
This is the simplest and easiest way to concatenate multiple cells in Google Sheets. The CONCATENATE function combines the values of two or multiple cells into a single one.
In the sample dataset, we have information about Employee ID in Column A, Department in Column B, First, Middle and Last name of the employees in Column C, D and E respectively. Using the CONCATENATE function, we will combine Column C, D and E and generate the Full Name of the employee. Follow the steps below.
Steps:
➤ Select F2 Cell, and put the following formula:
=CONCATENATE( C2,” “, D2,” “, E2)
➤ Press Enter and drag the F2 cell to fill the Full Name column.
Concatenate Multiple Cells Using the Ampersand (&) Operator
This method accomplishes the same task as the first method, with the added benefit of less typing. The Ampersand (&) operator in Google Sheets can combine multiple cells with different or identical separators together into a single cell. Using the same dataset, we will generate the Full Name of the employees, this time using the Ampersand(&) operator.
Steps:
➤ Select the F2 cell and put the formula:
=C2&” “&D2&” “&E2
➤ Next, hit Enter and drag the F2 cell to fill Column F.
Concatenate Multiple Cells Using the TEXTJOIN Function
The TEXTJOIN function is a more powerful and advanced tool for concatenating multiple cells. Unlike the CONCATENATE function and Ampersand (&) operator, the TEXTJOIN function can handle dynamic ranges and ignore empty cells. We will again work with the same dataset, but this time we will use the TEXTJOIN function to generate the Full Name of the employee.
Steps:
➤ Then, select the F2 cell and put the formula:
=TEXTJOIN(” “,TRUE,C2,D2,E2)
➤ Finally, press Enter and drag F2 cell to fill all the cells of F column.
Concatenate Multiple Cells Using the JOIN Function
The JOIN function of Google Sheets is another effective tool for concatenating multiple cells into a single one. Although it has a simpler formula and is faster to implement, it lacks the ability to ignore empty cells. We will work with the same dataset and aim to generate the Full Name of the employees by concatenating Columns C, D, and E.
Steps:
➤ Select Column F and in the F2 cell put the formula:
=JOIN(” “, C2:D2:E2)
➤ Press Enter to successfully concatenate Columns C, D, and E into Column F.
➤ Lastly, drag the F2 cell to fill all the cells of Column F.
Frequently Asked Questions
How do I Avoid Extra Spaces When Concatenating Cells?
If you are using CONCATENATE, TEXT or Ampersand (&) operator to combine cells, you have to manually make sure that there are no extra spaces or empty cells in your data. However, if you use the TEXTJOIN function, it will automatically ignore empty cells and avoid the extra space problem.
Why am I Getting #VALUE! Error?
This error may occur when you are trying to combine two different incompatible data types or when your formula references invalid cells. By using the TEXT function to format numbers and double-checking cell references, you can avoid this error.
Concluding Words
Concatenating cells in Google Sheets is often necessary for efficient data management and streamlining operations. In this article, we have discussed 4 effective methods of concatenating multiple cells in Google Sheets by using the CONCATENATE function, TEXTJOIN function, TEXT function and Ampersand (&) operator. Each method has its uses and benefits, making them suitable for different tasks depending on your requirements. Feel free to try out all four methods and select the ones that best fit your demands.
