Google Sheets is a powerful tool for storing, organizing, and accessing information in the form of cell ranges and strings. However, there are times when we need to combine or merge data from cell ranges or strings into a single unit to make it more understandable. Typing or copying cells individually into a single string to connect the information would take a lot of time. Who has time for that, right?
The solution in this situation is Google Sheets’ JOIN function. Don’t know what Google Sheets’ JOIN function is? You’ve come to the right place. Read on to discover everything you need to know about joining cells in Google Sheets.
What is the JOIN Function?
In Google Sheets, the JOIN function allows you to combine data from two or more tables into a single table. The JOIN function in Google Sheets takes at least two arguments: the first field contains the name of the table you want to join, and the second field contains the name of the column from each table that you want to join. The function will match the data in the specified columns and combine the data from all tables into a single table.
When to Use the JOIN Function?
In Google Sheets, the JOIN function allows you to combine data from multiple spreadsheets into a single master spreadsheet. Let’s say you already have a sheet with employee information and you want to create a new one with that information and their supervisor’s contact details. The JOIN function can be used to merge the data from both sheets into a single master sheet.
Syntax of the JOIN Function in Google Sheets
In Google Sheets, the JOIN function can be used to concatenate elements from one or more one-dimensional arrays with a specific delimiter. In Google Sheets, the JOIN function is a built-in function that belongs to the category of text functions.
=JOIN(delimiter, value_or_array1, [value_or_array2, ...])
Decoding the Arguments of the JOIN Function
The arguments of the JOIN function are:
-
delimiter– This argument is required. To join text values with a given delimiter, it can be a space, a comma, a hash character, or any other text string. It can also be an empty text string. -
value_or_array1– This is a required argument. It is one or more cells that you want to combine.
IMPORTANT NOTE: The JOIN function requires two inputs: array1 and delimiter. A delimiter is a text character that needs to be used between the concatenated array elements. Additionally, double quotation marks must be used to enclose the delimiter character. You can also specify an empty delimiter by providing an empty text string.
The first value to join is array1. It can be a single one-dimensional array, a cell range, string values, or a cell reference. You can also specify a delimiter character for the additional value or array that will be added.
Step-by-Step Tutorial on the JOIN Function in Google Sheets
If you have a data set that looks like this and you need to merge information from different cells into one cell, this step-by-step tutorial will teach you everything you need to know before getting started.
Method 1: To join the first name and last name separated by a space:
- Click on the cell where you want the merged information to appear.
- Type “=” and enter JOIN.
- Now, enter the delimiter between the quotation marks. In this case, a space.
- After the delimiter, enter a comma (,) and enter the cell names (separated by commas) that you want to join. In this case, the function would be =JOIN(” “,A3,B3).
An autofill option will now appear on the screen. Press CTRL + Enter to save time!
To join the city code and phone number separated by a dash:
- Click on the cell where you want the merged information to appear.
- Type “=” and enter JOIN.
- Now, enter the delimiter between the quotation marks. In this case, “-“.
- After the delimiter, enter a comma (,) and enter the cell names (separated by commas) that you want to join. In this case, the function would be =JOIN(” “,C3,D3).
Method 2: To join cells from a single string, use the following formula:
=JOIN(",",A2:D2)
Method 3: To join multiple cells from a cell range, use the following formula:
=JOIN("-", A2:A6,B2:B7)
Method 4: To join multiple cell ranges while ignoring empty cell ranges, use the following formula:
=JOIN(",", A2,A4:A6,B2,B4:B6)
Method 5: To join the cell range without ignoring empty cells, use the following formula:
=JOIN(",", A2:A6,B2:B6)
Double spaces between the commas show empty cells.
IMPORTANT NOTE: The JOIN function only allows you to join cells from a single cell range. So, if you want to join cells from a different column while typing the formula, you will need to add a comma and enter the other cell range (as shown in the above example).
When Not to Use the JOIN Function?
In some situations, you should avoid using JOIN in Google Sheets. For example, when trying to combine two tables with a different number of columns. The JOIN function will only include columns from the first table if the number of columns in both tables doesn’t match.
When trying to combine data from two separate sheets, you should also avoid using Google Sheets’ JOIN function. The JOIN function will not be able to combine data between sheets if they have distinct column headers.
Another Similar Function: TEXTJOIN
What is the TEXTJOIN Function?
The TEXTJOIN function combines text from multiple ranges and/or strings using a specified delimiter between each text value to be combined. This function efficiently combines ranges if the delimiter is an empty text string.
Syntax:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])
Decoding the Arguments of the TEXTJOIN Function
The arguments of the TEXTJOIN function are:
-
delimiter: The character or string of characters to be placed between the texts. -
ignore_empty: A TRUE or FALSE boolean. It controls empty cells within the selected range. -
text1: The array containing the text to be joined or the text itself.
Method 1: In this case, there are no empty spaces between the cells.
In the above screenshot, I used the formula:
=TEXTJOIN(",", TRUE, (A2:B5))
to combine multiple values.
Method 2: In this case, there are empty spaces between the cells.
In the above screenshot, I used the formula:
=TEXTJOIN(",", TRUE, (A2:B6))
to combine multiple values while ignoring empty spaces. If the formula uses the FALSE boolean when there are empty cells, a comma will appear in place of the empty cells. Like this:
In the above screenshot, I used the formula:
=TEXTJOIN(",", FALSE, (A2:B6))
Additional Method:
The CONCATENATE function does the same job as the TEXTJOIN function or the JOIN function with a different syntax from the other two. Google Sheets’ CONCATENATE function combines text from different cells into one cell.
Syntax:
=CONCATENATE(cell_range&delimiter&cell_range)
Decoding:
-
delimiter: The character or string of characters to be placed between the texts. -
cell_range: The name of the cells or cell ranges to be combined.
Here are some examples:
Example 1: TEXT with TEXT
In the above screenshot, I used the formula:
=CONCATENATE(A2&", "&B2)
to combine cell A2 and cell B2 separated by a comma.
Example 2: NUMBER AND TEXT
In the above screenshot, I used the formula:
=CONCATENATE(A2&"'s "&"D.O.B is "&B2)
to combine Emily’s first name and her date of birth, and then I used the same formula for all other cells.
Comparison between JOIN, TEXTJOIN, and CONCATENATE Functions
Without using the JOIN, CONCATENATE, or TEXTJOIN functions, you would have to copy and paste cells individually to merge the information into a single unit. Therefore, all of these functions are useful.
The only distinction between these three functions is that TEXTJOIN uses a boolean command after the delimiter, while the CONCATENATE function uses “&” between the delimiter (and the delimiter of the CONCATENATE function is between the names of the cells to be joined).
On the other hand, the JOIN function has no such requirements.
Conclusion
Now that you’ve learned all about Google Sheets’ JOIN function, you can try it out for yourself. I hope this article has provided you with enough information to use it successfully. In any case, you can always refer back to it if you have any doubts.
References:
- How to Merge Cells in Google Sheets
- How to Lock Cells in Google Sheets
- How to Split a Cell in Google Sheets
- How to Unmerge Cells in Google Sheets
- How to Group Rows and Columns in Google Sheets
