Day 5 – Naughty or nice? Splitting a list into first names and surnames

Oh deer!  Santa’s elves have given him an Excel list of children to deliver presents to. But the addresses are ordered by surname, and the list of children can only be sorted by first name.

Santa's list in Excel. There are two tables. In the first, column A show the children's names, with the first and surname together in the same cell, and column B shows whether they are Naughty or Nice. In the second table, column E shows the surnames, and column F shows the addresses.

Santa needs to split the names into separate first name and surname columns. Luckily, he has a couple of tricks up his sleeve.

Let’s insert two new columns by right-clicking on B in the column labels and choosing ‘Insert’. Now, into cell B2, paste the formula

=LEFT(A2,(FIND(” “,A2))-1)

This formula searches for a space in the name, and copies all the text up to the space.

Demonstrating the formula for the first name.

In cell C2, paste the formula

=RIGHT(A2,LEN(A2)-FIND(” “,A2))

This searches for the position of a space, subtracts it from the length of the text string and then copies all the text to the right of it.

Demonstrating the formula for the second name.

Now for the fun bit. Select the cells containing the formulae and hover over the corner until a black plus appears, then double-click.

The visual prompt for the auto-fill function.

This will auto-fill the formulae into the rest of the list.

The Excel spreadsheet with two columns added. Column B now shows first names, and column C surnames.

Finally, select the table, go to Sort & Filter under the Home tab, click on Custom Sort and sort by Column C.

The selected table and the Sort dialogue box with Column C selected.

Your table should now be ordered by surname, making Santa’s job a lot easier!

The final version of the Excel spreadsheet. Table 1 is now organised alphabetically by surname.