The JOIN() function in Google Sheets helps in combining the data from various cells into a single one. For example, if you have data in the form of the first names and the last names in two different cells, you can combine the first and name together in another cell using the JOIN() function. In this write-up, you will get to know how to use the JOIN() function in detail.
How to Use the JOIN Function in Google Sheets?
The syntax used for the JOIN() function in Google Sheets is as follows:
JOIN(delimiter, value_or_array1, [value_or_array2, ...])Example 1: Joining Elements from Different Cells
Consider that we have data in the form of persons’ first and last names in different cells in Google Sheets. We will use the JOIN() function to combine them in one cell.
Step 1: Type Join Function
Click on an empty cell where you want to join the data from different cells, and type the JOIN() function as =JOIN(:
Step 2: Type delimiter
Type the delimiter argument. In this argument, type the separation character in inverted commas that you want to use between the elements you want to join. This might be a comma, a hyphen, an asterisk, a full stop, or a space:
Step 3: Insert “value_or_array1” Argument
Place a comma to move to the next argument, and click on the cell you want to join. This will automatically type the cell’s reference address as the value_or_array1 argument in the JOIN() function:
Step 4: Insert “value_or_array2” Argument
Place a comma to move to the next argument, and click on the cell you want to join next. This will automatically type the cell’s reference address as the value_or_array2 argument in the JOIN() function:
Step 5: Press Enter
Now close the bracket and press Enter to reveal the result. This will join the elements of the cells whose reference addresses are added in the JOIN() function with a space between them:
Step 6: Apply JOINT() Function to Other Cells
Including the cell where we applied the JOIN() function, select the cells below to equal the number of cells on which you wish to apply the same function and press Ctrl + D:
Example 2: Joining Elements from Different Cells
Now consider another example, in which we will join the city codes and the telephone numbers placed in different cells in Google Sheets. To do that, simply type the JOINT() function and type all the required arguments in the function as mentioned above. In this example, we will use a Hyphen as a delimiter argument:
Now close the bracket and press Enter to reveal the result. This will join the elements of the cells whose reference addresses are added in the JOIN() function, with a hyphen between them:
Including the cell where we applied the JOIN() function, select the cells below to equal the number of cells on which you wish to apply the same function and press Ctrl + D:
Example 3: Apply JOIN() Function to Cell Range
In this example, we will apply the JOIN() function to a range of cells to combine all elements from them into a single cell. For this, we will use a cell range in a single row as a value_or_array1 argument in the JOIN() function as shown:
Now close the bracket and press Enter to reveal the result. This will join the elements of the selected cell range:
Similarly, we can select the cell range in a single column to join all the elements in them in a single cell, as follows:
Now close the bracket and press Enter to reveal the result. This will join the elements of the selected cell range:
Pro Tip: We can only select a single row or column in a cell range in the JOIN() function. Selecting multiple rows or columns in a single cell range in the JOIN() function will result in an error.
Example 4: Apply JOIN() Function to Multiple Cell Range
In this example, we will apply the JOIN() function to multiple ranges of cells to combine all elements from them into a single cell.
For this, we will use a cell range of a single row or column as the value_or_array1 argument and another row or column in the different cell range as the value_or_array2 argument in the JOIN() function as shown:
Now close the bracket and press Enter to reveal the result. This will join the elements of the selected cell range:
Example 5: Joining Cell Range with Having Cells
If we use the cell range to combine all the elements in them using the JOIN() function, it will return with a delimiter in place of the empty cells. If there are more conductive empty cells in the range, more delimiters will be used, as shown in the following:
Other Function in Google Sheets that Works Similar to JOIN() Function
There are some other functions in Google Sheets that do the same job as that of the JOIN() function does. Some of them are as follows.
- The TEXTJOIN() Function
- The CONCATENATE() Function
We will discuss the working of these functions one by one.
1: The TEXTJOIN() Function
The TEXTJOIN() function in Google Sheets does the job similar to the JOIN() function, but with the use of the TEXTJOIN() function, we can symmetrically get the result by ignoring the empty cells in the range.
The syntax to implement the TEXTJOIN() function is depicted below:
TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])Now we will use the TEXTJOIN() function for the above example to ignore the empty cells in the range to combine the data as a single cell. This will return the output without having any extra delimiter in place of the empty cells:
Similarly, if we do not want to ignore the empty cells in the selected range, we will use FALSE as the ignore_empty argument:
2: The CONCATENATE() function() Function
The CONCATENATE() function in Google Sheets works similarly to the JOIN() function, but with help of the CONCATENATE() function, we can add extra comments between the elements of the cells we want to join.
The practical usage of the CONCATENATE() function is demonstrated in the following screenshot:
Conclusion
The JOIN() function in Google Sheets is used to combine/merge the data from various cells into a single one. The syntax for the JOIN() function includes the delimiter and the value_or_array1. The delimiter is a special character/symbol utilized to separate the elements of various cells. If we do not use a delimiter, the JOIN() function will return with the elements of the cells without any space, comma, or any other character.
If there is an empty cell in the selected range, the JOIN() function returns with the delimiter in place of the empty cell in the output. We can use the TEXTJOIN() function, which works the same as the JOIN() function, to ignore the empty cells in the select range. To add additional comments between the elements of the cells we want to join, we use the CONCATENATE() function.