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
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:
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:
Copying the code to cells C3 to C8:
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:
It then becomes easy to count, for example, how many mails came from each organisation, by sorting and/or counting the domain values.