X
X
The Best Tech Newsletter Anywhere

Join 250,000 subscribers and get a daily digest of news, geek trivia, and our feature articles.

How to Concatenate Data from Multiple Cells in Google Sheets

The Google Sheets logo

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 CONCAT formula in a Google Sheets spreadsheet combining cells A6 and B6.

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.

The CONCATENATE function in Google Sheets linking cells together without operators.

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.

The CONCATENATE function with complex operators in a Google Sheets spreadsheet.

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.

The JOIN function merging arrays of cells together in a Google Sheets spreadsheet.

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.

The JOIN Function merging multiple arrays in a Google Sheets spreadsheet.

Ben Stockton Ben Stockton
Ben Stockton is a freelance tech writer from the United Kingdom. In a past life, he was a UK college lecturer, training teens and adults. Since leaving the classroom, he's been a tech writer, writing how-to articles and tutorials for MakeUseOf, MakeTechEasier, and Cloudwards.net. He has a degree in History and a postgraduate qualification in Computing.
Read Full Bio »

The above article may contain affiliate links, which help support How-To Geek.
How-To Geek is where you turn when you want experts to explain technology. Since we launched in 2006, our articles have been read more than 1 billion times. Want to know more?

x

The Best Tech Newsletter Anywhere

Join 250,000 subscribers and get a daily digest of news, comics, trivia, reviews, and more.