What is Split and Join Data from Multiple Columns in Google Sheets?

Google Sheets is a powerful and versatile spreadsheet application that allows you to store, manipulate, and analyze data. One of the common tasks that you may encounter when working with Google Sheets is to split and join data from multiple columns.

Splitting data means to separate the values in one column into two or more columns based on a delimiter, such as a comma, a space, or a dash. For example, if you have a column that contains full names, you may want to split it into two columns: one for the first name and one for the last name.

Joining data means to combine the values in two or more columns into one column, optionally with a separator in between. For example, if you have two columns that contain first names and last names, you may want to join them into one column that contains full names, with a space or a dash in between.

There are different ways to split and join data from multiple columns in Google Sheets, depending on your needs and preferences. In this article, we will show you some of the most useful methods and formulas that you can use to split and join data from multiple columns in Google Sheets.

How to Split Data from One Column into Multiple Columns in Google Sheets

There are two main ways to split data from one column into multiple columns in Google Sheets: using the SPLIT function or using the Split text to columns feature.

Using the SPLIT Function

The SPLIT function is a built-in function in Google Sheets that allows you to split a text string into multiple parts based on a delimiter. The syntax of the SPLIT function is:

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

text is the text string that you want to split.
delimiter is the character or characters that separate the parts of the text string. You can use more than one delimiter by enclosing them in double quotes, such as “,” or “-”. You can also use regular expressions to specify complex delimiters, such as “\s+” for any whitespace characters.
[split_by_each] is an optional argument that determines whether each character in the delimiter should be treated as a separate delimiter. The default value is FALSE, which means that the delimiter is treated as a whole. For example, if the delimiter is "- ", then the text string will be split only when both “-” and " " are present. If you set this argument to TRUE, then the text string will be split whenever either “-” or " " is present.
[remove_empty_text] is an optional argument that determines whether empty text values should be removed from the output. The default value is FALSE, which means that empty text values will be included as blank cells in the output. If you set this argument to TRUE, then empty text values will be ignored and no blank cells will be created.

For example, suppose you have a column of data that contains names and email addresses separated by commas, such as:

A
John Smith,john.smith@example.com
Jane Doe,jane.doe@example.com
Bob Lee,bob.lee@example.com

If you want to split this column into two columns: one for the names and one for the email addresses, you can use the following formula in cell B1:

=SPLIT(A1,",")

This will split the text string in cell A1 into two parts based on the comma delimiter and return the following output:

A	B	C
John Smith,john.smith@example.com	John Smith	john.smith@example.com

To apply this formula to the entire column, you can use an array formula with the ARRAYFORMULA function. The ARRAYFORMULA function allows you to perform calculations on multiple cells at once without copying and pasting formulas. The syntax of the ARRAYFORMULA function is:

=ARRAYFORMULA(array_formula)

array_formula is any formula that can operate on arrays or ranges of cells.

To use an array formula with the SPLIT function, you can use the following formula in cell B1:

=ARRAYFORMULA(SPLIT(A1:A3,","))

This will split the text strings in cells A1:A3 into two parts based on the comma delimiter and return the following output:

A	B	C
John Smith,john.smith@example.com	John Smith	john.smith@example.com
Jane Doe,jane.doe@example.com	Jane Doe	jane.doe@example.com
Bob Lee,bob.lee@example.com	Bob Lee	bob.lee@example.com

Note that you need to make sure that there are enough empty columns to the right of column B to accommodate the output of the SPLIT function. Otherwise, you may overwrite existing data or get an error message.

You can also use other arguments of the SPLIT function to customize your output. For example, if you want to split the text strings by each character in the delimiter, you can set the [split_by_each] argument to TRUE. For example, if you use the following formula in cell B1:

=ARRAYFORMULA(SPLIT(A1:A3,",",TRUE))

This will split the text strings in cells A1:A3 into three parts based on each character in the comma delimiter and return the following output:

A	B	C	D
John Smith,john.smith@example.com	John Smith	,	john.smith@example.com
Jane Doe,jane.doe@example.com	Jane Doe	,	jane.doe@example.com
Bob Lee,bob.lee@example.com	Bob Lee	,	bob.lee@example.com

Note that the comma delimiter is now included as a separate value in column C.

If you want to remove empty text values from the output, you can set the [remove_empty_text] argument to TRUE. For example, if you use the following formula in cell B1:

=ARRAYFORMULA(SPLIT(A1:A3,",",FALSE,TRUE))

This will split the text strings in cells A1:A3 into two parts based on the comma delimiter and remove any empty text values from the output. This will return the same output as the first example, except that there will be no blank cells in column C.

Using the Split text to columns Feature

Another way to split data from one column into multiple columns in Google Sheets is to use the Split text to columns feature. This is a built-in feature that allows you to quickly and easily split a column of data based on a delimiter of your choice. To use this feature, follow these steps:

Select the column of data that you want to split.
Go to the Data menu and choose Split text to columns.
A toolbar will appear below the column of data, where you can choose the delimiter that you want to use. You can choose from some common delimiters, such as comma, semicolon, space, or period, or you can choose Custom and enter your own delimiter.
The column of data will be instantly split into multiple columns based on the delimiter that you chose.

For example, suppose you have a column of data that contains names and email addresses separated by commas, such as:

A
John Smith,john.smith@example.com
Jane Doe,jane.doe@example.com
Bob Lee,bob.lee@example.com

If you want to split this column into two columns: one for the names and one for the email addresses, you can use the Split text to columns feature as follows:

Select column A.
Go to Data > Split text to columns.
Choose Comma as the delimiter from the toolbar.
The column of data will be split into two columns: one for the names and one for the email addresses.

The output will look like this:

A	B
John Smith	john.smith@example.com
Jane Doe	jane.doe@example.com
Bob Lee	bob.lee@example.com

Note that this feature will overwrite any existing data in the adjacent columns, so make sure that there are enough empty columns to accommodate the output. Also, this feature is not reversible, so if you want to undo it, you need to use Ctrl+Z or Command+Z on your keyboard.

How to Join Data from Multiple Columns into One Column in Google Sheets

There are also different ways to join data from multiple columns into one column in Google Sheets, depending on your needs and preferences. In this article, we will show you some of the most useful methods and formulas that you can use to join data from multiple columns in Google Sheets.

Using the & Operator

The & operator is a simple and easy way to join data from multiple columns into one column in Google Sheets. The & operator allows you to concatenate or join two or more values together with an optional separator in between. The syntax of the & operator is:

=value1 & [separator] & value2 & [separator] & ...

value1, value2, etc. are the values that you want to join together. They can be text strings, numbers, cell references, or formulas.
[separator] is an optional argument that specifies the character or characters that you want to insert between the values. You need to enclose them in double quotes, such as " " or “-”.

For example, suppose you have two columns of data that contain first names and last names, such as:

A	B
John	Smith
Jane	Doe
Bob	Lee

If you want to join these two columns into one column that contains full names with a space in between, you can use the following formula in cell C1:

=A1 & " " & B1

What is Split and Join Data from Multiple Columns in Google Sheets?