The SPLIT Function

• Introduction
• Method

Breaking down text values into their constituent parts using the SPLIT function.

Introduction

Google Sheets now has the built-in ability to split data in an existing spreadsheet, or even when you paste ctrl-v in new data. If you have more complex or specific requirements, you can also achieve a similar outcome by using the split function.

Method

Imagine that you have a set of names, like the following, that you want to split (decompose) into their constituent parts (e.g. given name and family name).

Using SPLIT

The easiest way to accomplish this (mirroring the functionality offered within Google Sheets itself) is to use the split function to expand the text into an array of values split by the space character.

SPLIT(A2, " ", TRUE)

This will output all the constituent parts of the name into adjacent horizontal cells. If any of the destination cells have existing content, you will get an error until you clear them. The output will take the form of a ‘jagged array’, such as the one below.

If you would like to transform the direction of these cells (e.g. have the names split out to a vertical, rather than horizontal, list) then you can use the transpose function to achieve this (as below).

TRANSPOSE(SPLIT(A2, " ", TRUE))

Using FIND

The split function works well for simple names (e.g. John Doe), but with names that include middle names, it will create a list that will need further processing because the second column may sometimes contain family names, and sometimes middle names. You can improve on this in a number of ways. Firstly by using a combination of the left, find, len and mid functions to ‘find’ the first space and output the text before it (the first formula) and also after it (the second formula). The table below illustrates the output.

LEFT(A2, FIND(" ", A2, 1) - 1)

MID(A2, FIND(" ", A2, 1) + 1, LEN(A2) - FIND(" ", A2, 1))

Using SPLIT and INDEX

The solution above is useful, but if you only want the ‘first’ and the ‘last’ part of the name, then split can be used in conjunction with index as part of an array formula to do just that. In the examples below, the first outputs the first element of the split array (1) and the second outputs the last element (using counta to determine the length). The braces are used to convert the output of the split function so it can be properly understood by the index function (an array formula). The table that follows the formula shows the results, which gives the first and last names in their correct columns, irrespective of the number of middle names in the source data.

INDEX({SPLIT(A2, " ", FALSE)},1)

INDEX({SPLIT(A2, " ", FALSE)}, COUNTA(SPLIT(A2, " ")))