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

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

However, in some cases you may come across a situation where data from a single cell range or string needs to be combined or merged into a single unit to make sense. Typing or copying cells individually into a single string to combine information is very time consuming. Who has time for that?

The solution for this situation is the JOIN Google Sheets function. Not sure what his JOIN function in Google Sheets functions is?You are in the right place. To understand everything there is to know about how to merge cells in Google Sheets, read on.

What Is the JOIN Function?

Google Sheets allows you to combine data from two or more tables into a single table using the JOIN function. The join table function in Google Sheets accepts at least two arguments. The first field contains the names of the tables to join and the second field contains the names of the columns in each table to join. This function matches data in specified columns and combines data from all tables into one table.

When to Use the JOIN Function

Google Sheets allows you to combine data from multiple sheets into a single master sheet using the JOIN function. Suppose you already have a sheet with information about your employees and you want to create a new sheet with that information and the manager’s contact information. You can combine data from both sheets into one master sheet using the JOIN function.

Google Sheets JOIN Function Syntax

In Google Sheets, you can use the JOIN function to concatenate the elements of one or more one-dimensional arrays with specific delimiters. In Google Sheets, the JOIN function is a built-in function that belongs to the text functions category.

=JOIN (delimiter, value or array 1, [value or array 2, …])

Breakdown

The arguments for the JOIN function are:

Delimiter – This is a required argument. Spaces, commas, hash characters, or other text strings can be used to join text values ​​with a specific delimiter. It can also be an empty string.

value_or_array1 is a required argument. Single or multiple cells to merge.

Important Note: The JOIN function requires two inputs: array1 and delimiter. A delimiter is a text character that must be used between concatenated array elements. Additionally, you must use double quotes to enclose delimiters. You can also specify a blank delimiter by specifying an empty string. The first value to be combined is array1. Can be a single one-dimensional array, cell range, text string value, or cell reference. You can also specify a delimiter for additional values ​​or arrays to be added.

Google Sheets JOIN Function – Step-By-Step Tutorial

If you have a data set like the one below and need to combine information from different cells into one cell, this step-by-step tutorial will show you everything you need to know before you start.

Method 1

To combine first name and last name separated by a space:

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

Step 2: Press “=” and type “JOIN.

Step 3: Enter the delimiter in “”. In this case it is “space”.

Step 4: Type a comma (,) after the delimiter and type the names of the cells you want to merge (separated by commas). In this case the function would be =JOIN(” “,A3,B3).

Autofill options appear on the screen. Save time by pressing CTRL + Enter!

To join the area code and phone number separated by a hyphen:

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

Step 2: Press “=” and type “JOIN.

Step 3: Next, enter the delimiter between the apostrophes in this case “-”.

Step 4: Type a comma (,) after the delimiter and type the names of the cells you want to merge (separated by commas). In this case the function would be =JOIN(” “,C3,D3).

Method 2

To merge single string cells, use the following formula:

=JOIN(“, ”,A2:D2)

Method 3

To merge multiple cells in a range of cells, use the following formula:

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

Note: If the cell ranges have different column numbers, you must enter the individual cell ranges when using the JOIN function as shown in the example above.

Method 4

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

=JOIN(“,”, A2,A4:A6,B2,B4:B6)

Method 5

To merge a range of cells without ignoring empty cells, use this formula:

=JOIN(“,”, A2:A6,B2:B6)

A double comma spacing indicates an empty cell.

Important Note: The JOIN function can only combine cells from a single cell range. Therefore, if you want to merge cells from other columns when entering the formula, you will need to add a comma and enter the other cell range (see example above).

When to Not Use the JOIN Function

In some situations, you should avoid using JOINs in Google Sheets. For example, try to join two tables that have different numbers of columns. The JOIN function only includes columns from the first table if the number of columns in the two tables does not match.

Also avoid using the JOIN cell function in Google Sheets if you are trying to combine data from two different sheets. The JOIN function cannot combine data between sheets if the sheets have separate column headers.

Another Similar Function: TEXTJOIN

What Is the TEXTJOIN Function?

Multiple ranges or strings of text are joined using the TEXTJOIN function. This function allows you to specify a delimiter between each text value to be joined. This function effectively joins ranges if the delimiter is an empty text string.

Syntax

The Google Sheets TEXTJOIN function joins text from multiple arrays where the joined text can be separated by a specified delimiter. If you have an empty cell range between the cells you are trying to merge, the formula to use is the TEXTJOIN function!

Enter “TEXTJOIN(delimiter,ignore_empty,text1,[text2,…])” in the formula bar of the selected cell range and text.

Breakdown

Delimiter: A character or string to place between text.

ignore_empty: Boolean TRUE or FALSE. Controls blank cells in the selected range.

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

Method 1

In this case there are no spaces between cells.

The screenshot above uses the following formula:

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

Combines multiple values.

Method 2

In this case, there will be blank spaces between the cells.

The screenshot above uses the following formula:

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

Combine multiple values ​​ignoring empty spaces. If the formula uses the Boolean value FALSE when there are empty cells, commas are displayed instead of empty cells. It seems like:

In the screenshot above I am using the following formula

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

Additional Method

The CONCATENATE function performs the same function as the TEXTJOIN or JOIN functions, but with a different syntax form than the other two. The CONCATENATE function in Google Sheets combines text from different cells into a single cell.

Syntax

=CONCATENATE(cell range&delimiter&cell range)

Breakdown

Delimiter: A character or string to place between text.

Cell_range: Name of the cell or cell range to merge.

Here are some examples:

Example 1: Text to Text

In the screenshot above I am using the following formula

=CONCATENATE(A2& “, ” &B2)

Combine cells A2 and B2 by separating them with a comma.

Example 2: numbers and text

In the screenshot above I used:

 =CONCATENATE(“&”DOB in A2&” is “&B2)

I used a formula to combine Emily and her date of birth and used the same formula for all other cells.

Comparison Between the JOIN, TEXTJOIN, and CONCATENATE Function

If you don’t use the JOIN, CONCATENATE, or TEXTJOIN functions, you must copy and paste the cells individually to combine the information into one unit. So all these features are useful.

The only difference between these three functions is that the TEXTJOIN uses a boolean command after the delimiter, whereas the CONCATENATE function uses an ‘&’ between the delimiters (and the delimiter in the CONCATENATE function characters are placed between the name cells to be merged).

The JOIN function, on the other hand, has no such requirement.

Conclusion

Now that you’ve learned all about the JOIN function in Google Sheets, let’s try it out. We hope this article has provided you with enough information to use it successfully. In any case, you can always refer to it if you have any doubts.