Google has a handy little formula that does a lot of the work for us: CONCATENATE . It’s a handy formula to have, even if it’s only used to link cells. Alas, if you’re using Google Sheets, you won’t be able to use CONCATENATE , because it requires a formula language like FEEDBACK . To use CONCATENATE , we must first enable the FEEDBACK language in the formula editor: After this step, we can use CONCATENATE in a formula:
Many Google Sheets users may be aware that you can link cells in Google Sheets. The function to do this is called concatenate and it is a very useful tool for building formulas in Google Sheets.
Google Sheets is a spreadsheet program for many users. Like its competitor, the online alternative to Microsoft Excel offers a host of functions that make calculating data as easy as falling off a tree trunk. One of these features is an introduction to using CONCATENATE in Google Sheets. The CONCATENATE function combines data from multiple cells and stores it in a single cell. In addition to the concatenation formula, you can also use the CONCAT and JOIN functions to combine cells in Google Sheets. These functions vary in complexity, but they are there to provide the same capability: help combine data in a way that makes large data sets easily manageable. You must also link cells in Google Sheets when using the CONCATENATE function.
To use the CONCATENATE feature of Google Sheets
For better understanding, we will start with the CONCATENATE function, then the CONCAT or JOIN functions. Let’s see how we can use these functions to combine data and link individual cells in Google Sheets.
3 steps to using the CONCATENATE feature in Google Sheets
1. Open a Google Sheets document. 2. Enter the CONCATENATE function in the cell. 3. Press the Enter key to see the result.
Details for CONCATENATE in Google Sheets
Now that you understand the basics of using CONCATENATE in Google Sheets, let’s look at the process in detail with images for better understanding.
1. How to use the basic CONCATENATE feature in Google Sheets
The CONCATENATE feature in Google Sheets offers more flexibility than the previous feature. You don’t need to add extra or extra spaces to indicate separation in linked data, because you can use operators to do so.
Step 1. Entering the CONCATENATE formula
The Google spreadsheet below consists of parts of the last name, spread over several columns. We will use CONCATENATE in Google Sheets to combine the contents of the cells. The basic syntax of this formula is given below. =CONCATENATE(string 1, string 2,…) First, enter the function as shown in the following figure. =CONCATENATE(A2,B2,C2) alt=GECONCATENATE in Google Sheets data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/How-To-Use-Concatenate-in-Google-Sheets-To-Link-Cells.png />Enter the CONCATENATE function You can also use the ampersand (&) instead of the comma (,) to combine data from separate cells.
Step 2. Press Enter for results
Now press the Enter key to calculate the results. This is what the value should look like in D2. alt=Display results data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1624060139_50_How-To-Use-Concatenate-in-Google-Sheets-To-Link-Cells.png />Display results
Insert a space in the CONCATENATE function
The above results are not perfect because spaces must be inserted between terms in different columns to make the name distinguishable and easy to read. To insert a space, use the following formula. =CONCATENATE(A2&&B2&&C2) The ampersand (&) serves as the link character, and the space character ( ) indicates the space to be left after a particular term. alt=Enter the link function in Google Sheets with a space data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1624060140_164_How-To-Use-Concatenate-in-Google-Sheets-To-Link-Cells.png />Enter the function with a space Now press Enter to get the results in another cell. alt=Show Results data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1624060141_448_How-To-Use-Concatenate-in-Google-Sheets-To-Link-Cells.png />Show Results Once you have the result in the first cell, drag the formula to the other cells and get the result for the same cells.
2. use CONCATENATE with line feed
The line break allows you to align merged cells in Google Sheets. Here is a special function to insert a line break. =CHAR(10) In the following Google Sheet image, we need to construct an address by combining data from multiple cells using the row transfer function. alt=address sheet data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1624060142_132_How-To-Use-Concatenate-in-Google-Sheets-To-Link-Cells.png />address sheet
Step 1. Enter the CONCATENATE formula with a line break
First, open a spreadsheet and enter the CONCATENATE formula in Google Sheets as shown below. =CONCATENATE(A2&&B2,CHAR(10),C2,CHAR(10),D2) The term A2&B2 is used to keep the names together on one line. The linefeed function to B2 will place the contents in cell C2 under the name. alt=Add formula CONCATENATE data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1624060143_327_How-To-Use-Concatenate-in-Google-Sheets-To-Link-Cells.png />Add formula CONCATENATE
Step 2. Get results
Press the Enter key to get the results of the function. You can use drag and drop to get the results of all cells after you get the result in the first cell. alt=Show summarized addresses data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1624060144_751_How-To-Use-Concatenate-in-Google-Sheets-To-Link-Cells.png />Show summarized addresses
3. how to use the CONCATENATE function with sequential numbers
Consecutive numbers means that consecutive cells are assigned a numerical format in ascending order. This can be done with CONCATENATE in Google Sheets. In the following data table, combine the first and last name cells and display multiple values in the cells of column C. You must also assign numbers 1 to all names. alt=name sheet data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1624060145_397_How-To-Use-Concatenate-in-Google-Sheets-To-Link-Cells.png />name sheet
Step 1. Entering a formula for successive numbers
The formula for consecutive numbers is given below. =CONCATENATION(LINE()-1, -,A2,,B2) Here, the ROW()-1 term specifies the sequence numbers to be assigned to the terms. alt=Add current number concatenation formula in Google Sheets data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1624060146_547_How-To-Use-Concatenate-in-Google-Sheets-To-Link-Cells.png />Add current number formula
Step 2. Press Enter to get the results
Now press the Enter key to calculate the results. Once you have the result in the first cell, you can either use the drag and drop feature to apply the formula to all subsequent cells, or choose the autofill option that appears after you press Enter. alt=Check of current numbers and compound terms data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1624060148_731_How-To-Use-Concatenate-in-Google-Sheets-To-Link-Cells.png />Check of current numbers and compound terms
4. How to use the CONCAT feature in Google Sheets
The CONCAT function is the easiest way to combine data from two cells, but it has two drawbacks. First, only data from two cells can be merged, and second, no operators can be used to determine how the linked data should be displayed. The basic syntax of the CONCAT formula is given below. =CONCAT(line 1, line 2)
Step 1. Select a cell and enter the formula
First, open the data table and select the empty cell in which you want to display the linked data. Here we will use CONCATENATE in Google Sheets to merge the first and last name of the two cells into separate cells. Enter the following formula in an empty cell. =CONCAT(A2,B2) alt=Enter formula in cell data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1624060149_479_How-To-Use-Concatenate-in-Google-Sheets-To-Link-Cells.png />Enter formula in cell Now make sure that all cell values in column B start with a space. This is necessary because the CONCAT formula in Google Sheets does not allow operators to insert spaces between cell data.
Step 2. Search results
Now that you have pressed Enter, the merged data appears in column C. alt=Show results for concatenate in google sheets data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1624060150_993_How-To-Use-Concatenate-in-Google-Sheets-To-Link-Cells.png />Show results
5. how to use the JOIN function
Google Sheets’ JOIN feature is also one of the best features to use when working with large data tables. This function has the peculiarity that the user can specify a generic operator, also called separator, to be applied after each term of a formula. Let’s illustrate this with an example. Here is an example of CONCATENATE in Google Sheets. Note that you must use a comma (,) after each term to connect them. =CONCATENATE(A2,B2,C2) alt=Use the CONCATENATE feature in Google Sheets data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1624060151_507_How-To-Use-Concatenate-in-Google-Sheets-To-Link-Cells.png />Use the CONCATENATE feature in Google Sheets Check the results of the CONCATENATE function in Google Sheets. The contents of cells A2, B2 and C2 are displayed in cell D2. alt=Check value of column D for concatenation in Google Sheets data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1624060152_702_How-To-Use-Concatenate-in-Google-Sheets-To-Link-Cells.png />Check value of column D You can see here how to write the same formula using the JOIN function. The general syntax of this function is JOIN(separator,range). The formula for the JOIN function for the above equation would be as follows. =JOIN(,,A2:D2) Here, the comma (,) in quotes is the delimiting operator applied after each term of the value range. The string A2:D2 is the range of cells the function is applied to in Google Sheets. alt=Use JOIN with corresponding cell reference data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1624060153_973_How-To-Use-Concatenate-in-Google-Sheets-To-Link-Cells.png />Use JOIN with corresponding cell reference Press the Enter key to accept the results. You can use the table formula with the CONCATENATE function to get results for all cells. alt=Show result value of concatenation in Google Sheets data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1624060154_323_How-To-Use-Concatenate-in-Google-Sheets-To-Link-Cells.png />Show result value It is important to remember that the JOIN function always works with a range of cells, not individual cells, and is therefore best suited for large data tables. You can also add multiple cell ranges to a single formula if you want to use the same separator for all ranges.
6. How to split text cells in Google Sheets
If you’ve linked all the cells and want to return them to their original state, you can use Google Sheets’ split feature. You can follow the detailed tutorial on how to split text into columns in Google Sheets.
Supplement
Linking cells to data in Google Sheets is a common requirement for many users. Therefore, you need to know how to use CONCATENATE in Google Sheets to better manage large data sets. Although there are many ways to perform these tasks, the chaining statement is the most commonly used for this purpose. You can also link cells in Google Sheets by using equations that contain the ampersand symbol (&), as shown above. The CONCAT function is the easiest to use, followed by CONCATENATE or JOIN. You can also use the QUERY feature to combine columns in Google Sheets. Ultimately, however, it is up to the users to decide whether or not to use formulas.
Related Tags:
google sheets concatenate multiple cellsgoogle sheets merge cells keep datagoogle sheets add text to cellgoogle sheets concatenate arrayformulagoogle sheets concatenate(if)google spreadsheet concatenate with separator,People also search for,Privacy settings,How Search works,google sheets concatenate multiple cells,google spreadsheet concatenate with separator,google sheets concatenate text and formula,google sheets concatenate number and string,google sheets merge cells keep data,google sheets add text to cell,google sheets concatenate arrayformula,google sheets concatenate(if)
