How to Use the Google Sheets JOIN Function (with Video)

Google Sheets can store, organize and access information in terms of cell ranges and strings.

However, sometimes we face a situation where the data from a singular cell range or string needs to be combined or merged into one unit to make sense. Now it would take a lot of time to individually type or copy cells into one string to connect the information. Who has the time for it, am I right?

The solution in this situation is the JOIN Google Sheets function. Don’t know what the JOIN function Google Sheets function is? You’re in the right place. Read along to find out everything you need to know about how to join cells in Google Sheets.

What Is the JOIN Function?

In Google Sheets, the JOIN function lets you combine data from two or more tables into a single table. The Google Sheets join tables function accepts 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 in each table you want to join. The function will match the data in the specified columns and combine the data from all of the tables into a single table.

When to Use the JOIN Function

In Google Sheets, the JOIN function lets you combine data from multiple sheets into a single master sheet. Let’s say you already have a sheet with information about employees and want to make a new one with that information and the contact information for their manager. The JOIN function can be used to merge the data from both sheets into a single master sheet.

Google Sheets JOIN Function Syntax

In Google Sheets, the JOIN function can be used to concatenate the elements of one or more one-dimensional arrays with a particular delimiter. In Google Sheets, the JOIN function is a built-in function that belongs to the Text Function category.

=JOIN (delimiter, value_or_array1, [value_or_array2, …])

Breakdown

Where the arguments for the JOIN function are:

delimiter – This is a required argument. To join text values with a given delimiter, it can be a space, comma, hash character, or any other text string. It can also be an empty string.

value_or_array1 is a mandatory argument. A single or multiple cells that you want to combine.

IMPORTANT NOTE: The JOIN function requires two inputs: array1 and delimiter. A delimiter is a text character that should be used between concatenated array elements. Additionally, double quotes should be used to enclose the delimiter character. You can also specify a blank delimiter by providing an empty string. The first value to be joined together is array1. It can be a single one-dimensional array, a cell range, text string values, or a cell reference. You can also specify a delimiter character for the additional value or array that will be appended.

Google Sheets JOIN Function – Step-By-Step Tutorial

If you have a data set that looks like the following one, and you need to merge the information from different cells into one cell, then this step-by-step tutorial will teach you everything you need to know before starting.

Sample data for the join function

Method 1

To join First name and last name separated by a space:

Step 1: Click on the cell where you want the merged information to be.

Step 2: Press “=” and type JOIN.

Step 3: Now enter the delimiter in “”. In this case a “space”.

Step 4: After the delimiter enter a comma (,) and enter the name of the cells (separated by a comma) that you would like to join. In this case, the function would be =JOIN(“ ”,A3,B3).

Google Sheets Join Function results

An autofill option will now appear on your screen. Press CTRL + Enter to save time!

To join the city code and telephone number separated by a hyphen:

Step 1: Click on the cell where you want the merged information to be.

Step 2: Press “=” and type JOIN.

Step 3: Now enter the delimiter in between the apostrophes. In this case “-”.

Step 4: After the delimiter, enter a comma (,) and enter the name of the cells (separated by a comma) that you would like to join. In this case, the function would be =JOIN(“ ”,C3,D3).

Google sheets join function for numbers

Method 2

To join cells from a single string, use the following formula:

=JOIN(“, ”,A2:D2)
Using the Google Sheets Join Function for lists

Method 3

To join multiple cells from a cell range, use the following formula:

=JOIN(“-“, A2:A6,B2:B7)
Using a - delimiter

NOTE: When your cell ranges differ in column numbers, you will have to type in the individual cell ranges while using the JOIN function, as shown in the example above.

Method 4

To join multiple cell ranges and ignore the empty cell ranges, use the following formula:

=JOIN(",", A2,A4:A6,B2,B4:B6)
Join while eliminating spaces

Method 5

To join the cell range without ignoring the empty cells, use the following formula:

=JOIN(“,”, A2:A6,B2:B6)
Not excluding blank spaces

The double comma spacing shows the empty cells.

IMPORTANT NOTE: The JOIN function only lets you join cells from a singular cell range. So, if you want to join cells from the other column while typing in the formula, you will have to add a comma and enter the other cell range (as shown in the example above).

When to Not Use the JOIN Function

In some situations, you should avoid using JOIN in Google Sheets. When you try to combine two tables with different numbers of columns, for instance. The JOIN function will only include the columns from the first table if the number of columns in the two tables does not match.

When attempting to combine data from two distinct sheets, you should also avoid using the Google Sheets JOIN cells function. The JOIN function will not be able to combine the data between the sheets if they have distinct column headers.

Another Similar Function: TEXTJOIN

What Is the TEXTJOIN Function?

The text from multiple ranges and/or strings is combined using the TEXTJOIN function, which allows you to specify a delimiter between each text value that will be combined. This function effectively combines the ranges if the delimiter is an empty text string.

Syntax

Google Sheets TEXTJOIN function joins text from multiple arrays where the joined texts can be separated by a given delimiter. If you have empty cell ranges between the cells that you are trying to combine, then the TEXTJOIN function is the formula to use!

Type in: “TEXTJOIN(delimiter, ignore_empty, text1, [text2, …])” in the formula bar for selected cell ranges and strings.

Breakdown

Delimiter: The character or string to place between texts.

ignore_empty: A Boolean TRUE or FALSE. It controls blank cells in the selected range.

text1: The array containing the text to join or the text itself.

Method 1

In this case, there are no blank spaces between the cells.

using TEXTJOIN in Google Sheets

In the screenshot above, I have used the formula:

=TEXTJOIN(“,”,TRUE,(A2:B5))

to combine the multiple values together.

Method 2

In this case, there are blank spaces between the cells.

TEXTJOIN with a true operator

In the screenshot above, I have used the formula:

 =TEXTJOIN(“,”,TRUE,(A2:B6))

to combine the multiple values while ignoring the empty spaces. If the formula uses the Boolean FALSE when there are blank cells, a comma appears instead of the empty cells. Like so:

Textjoin with a false operator

In the screenshot above, I have used the formula

=TEXTJOIN(“,”,FALSE,(A2:B6))

Additional Method

The CONCATENATE function does the same job as the TEXTJOIN function or JOIN function with a different syntax format than the other two. The CONCATENATE function in Google Sheets combines the text from different cells into one cell.

Syntax

=CONCATENATE(cell_range& delimiter &cell_range)

Breakdown

delimiter: The character or string to place between texts.

Cell_range: The name of the cells or cell ranges to be combined.

Here are some examples:

EXAMPLE 1: TEXT with TEXT

CONCATENATE data

In the screenshot above, I have used the formula

=CONCATENATE(A2& “, ” &B2)

to combine cells A2 and B2 separated by a comma.

EXAMPLE 2: NUMBER AND TEXT

use concatenate for text and numbers

In the screenshot above, I have used the:

 =CONCATENATE(A2&"'s "&"D.O.B is "&B2)

formula to combine Emily and her D.O.B and then used the same formula for all other cells.

Comparison Between the JOIN, TEXTJOIN, and CONCATENATE Function

Without using the JOIN, CONCATENATE, or TEXTJOIN functions, you will have to individually copy and paste cells to merge the information into one unit. Therefore, all of these functions come in handy.

The only distinction between these three functions is that TEXTJOIN uses a Boolean command after the delimiter, whereas the CONCATENATE function uses “&” between the delimiter (and the delimiter for the CONCATENATE function comes in between the name cells to be joined).

On the other hand, the JOIN function has no such requirements.

Conclusion

Now that you have learned all about the JOIN function in Google Sheets, you can try it out for yourself. Hopefully, this article has provided enough information for you to use it successfully. Either way, you can always refer back when in doubt.

Most Popular Posts

Talha Faisal

Talha Faisal

Talha is a seasoned technical writer that specializes in Automation and SaaS. Google Sheets is central to his work, and he uses his writing to make Google Sheets easy to use for everyone.
Related Posts