Split Text into Columns in Google Sheets

I often find that when I have a spreadsheet containing people’s names, the first and last names end up being stored together in the same cell. This isn’t always ideal; sometimes I’ll want to sort them alphabetically by surname, or use the first names to merge into the ‘Dear [firstname]’ part of a letter.

A handy way to move them into separate columns is to use the SPLIT function. The general syntax is

SPLIT(text, delimiter)

where text is the content you want to split up (either the string itself, contained within quotes, or a reference to it, such as a cell address) and delimiter is the character you want to find in the string and use to define where the split should occur.

Here’s an example sheet with some names listed in a single column:

Click image to enlarge

Click image to enlarge

We want to split each name where the ‘space’ character occurs, so here’s our SPLIT function for cell C2:

=SPLIT(A2, " ")

And here’s the result:

Click image to enlarge

Click image to enlarge

Copying the code to cells C3 to C8:

Click image to enlarge

Click image to enlarge

We now have each name separated into two columns, ready to be processed independently.

UPDATE: As I meant to point out in this article, this technique is useful for email addresses too, to divide the mailbox part from the domain part:

=SPLIT(A10, "@")
Click image to enlarge

Click image to enlarge

It then becomes easy to count, for example, how many mails came from each organisation, by sorting and/or counting the domain values.