Home » General » How to Concatenate Data from Multiple Cells in Google Sheets

How to Concatenate Data from Multiple Cells in Google Sheets

by Walter Jules

In Google Sheets, if you want to link data from multiple cells together, you don’t have to merge them. You can use the CONCAT, CONCATENATE, and JOIN functions to combine them in one cell.

These functions range from the simplistic (CONCAT) to the complex (JOIN). CONCATENATE offers the most flexibility, as it allows you to manipulate the linked data with operators and additional content.

How to Use the CONCAT Function

You can use the CONCAT function to combine the data from two cells, but it has limitations. First, you can only link two cells, and it doesn’t support operators to configure how to display the linked data.

To use CONCAT, open your Google Sheets spreadsheet and click an empty cell. Type =CONCAT(CellA,CellB), but replace CellA and CellB with your specific cell references.

In the example below, CONCAT combines text and numeric values.

The text from cells A6 and B6 (“Welcome” and ” To”, including the space at the start of the B6 cell) are shown together in cell A9. In cell A10, the two numeric values from cells B1 and C1 are shown together.

While CONCAT will combine two cells, it doesn’t allow you to do much else with the data. If you want to combine more than two cells—or modify how the data is presented after you combine them—you can use CONCATENATE instead.

How to Use the CONCATENATE Function

The CONCATENATE function is more complex than CONCAT. It offers more flexibility for those who want to combine cell data in different formats. For example, CONCAT doesn’t allow you to add additional text or spaces, but CONCATENATE does.

To use CONCATENATE, open your Google Sheets spreadsheet and click an empty cell. You can use CONCATENATE in several ways.

To link two or more cells in a basic way (similar to CONCAT), type =CONCATENATE(CellA,CellB) or =CONCATENATE(CellA&CellB), and replace CellA and CellB with your specific cell references.

If you want to combine an entire cell range, type=CONCATENATE(A:C), and replace A:C with your specific range.

The ampersand (&) operator allows you to link cells in a more flexible way than CONCAT. You can use it to add additional text or spaces alongside your linked cell data.

In the example below, the text in cells A6 to D6 has no spaces. Because we used the standard CONCATENATE function without the ampersand, the text is displayed in cell C9 as one word.

To add spaces, you can use an empty text string (“”) between your cell references. To do this using CONCATENATE, type =CONCATENATE(CellA&" "&CellB&" "&CellC&" "&CellD), and replace the cell references with yours.

If you want to add additional text to your combined cell, include it in your text string. For example, if you type =CONCATENATE(CellA&" "&CellB&" Text"), it combines two cells with spaces between them and adds “Text” at the end.

As shown in the example below, you can use CONCATENATE to combine cells with text and numeric values, as well as add your own text to the combined cell. If you’re only combining cells with text values, you can use the JOIN function instead.

How to Use the JOIN Function

If you need to combine large arrays of data in a spreadsheet, JOIN is the best function to use. For example, JOIN would be ideal if you need to combine postal addresses that are in separate columns into a single cell.

The benefit of using JOIN is that, unlike CONCAT or CONCATENATE, you can specify a delimiter, like a comma or space, to be placed automatically after each cell in your combined single cell.

To use it, click an empty cell, type =JOIN(",",range), and replace range with your chosen cell range. This example adds a comma after each cell. You can also use a semicolon, space, dash, or even another letter as your delimiter if you prefer.

In the example below, we used JOIN to combine text and numeric values. In A9, the array from A6 through D6 is merged using a simple cell range (A6:D6) with a space to separate each cell.

In D10, a similar array from A2 to D2 combines text and numeric values from those cells using JOIN with a comma to separate them.

You can use JOIN to combine multiple arrays, too. To do so, type =JOIN(" ",A2:D2,B2:D2), and replace the ranges and delimiter with yours.

In the example below, cell ranges A2 to D2, and A3 to D3 are joined with a comma separating each cell.

You may also like

Leave a Comment